Is it possible to add index to a temp table? And what's the difference between create #t and declare @t Is it possible to add index to a temp table? And what's the difference between create #t and declare @t sql sql

Is it possible to add index to a temp table? And what's the difference between create #t and declare @t


#tablename is a physical table, stored in tempdb that the server will drop automatically when the connection that created it is closed, @tablename is a table stored in memory & lives for the lifetime of the batch/procedure that created it, just like a local variable.

You can only add a (non PK) index to a #temp table.

create table #blah (fld int)create nonclustered index idx on #blah (fld)


It's not a complete answer but #table will create a temporary table that you need to drop or it will persist in your database. @table is a table variable that will not persist longer than your script.

Also, I think this post will answer the other part of your question.

Creating an index on a table variable