Some questions never get old. One of them is temp table variable and temp table in SQL Server.
I have previously wrote about this indepth here : SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable
Recently I received question:
Can temporary table have indexes? If yes, are they really useful and efficient? When nonclustered index are created a separate table is created, what happens in the case of when temporary table?
I really liked the question of user.
Yes, temporary table can have indexes. If you have to use temporary table more than one time in your operation, create index on it and it will be have performance improvement because of Index usage.
When indexes are created on temporary table (which are stored in TempDB), indexes are created on TempDB as well. When original table is dropped the index table may be or may not be dropped, but definitely become not useful. The same index can not be used again with new temp table.
I am interested in listening my blog readers comment about these questions.
Reference : Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
hello pinal..could you plese tell me something about
select 8 statement from where the data is retrived.
and when we run
select 1 2 3 4 statement it will give error
help me..
Creating indexing on the temp table doesn’t come up with index seek (instead it uses table scan) in the “Estimated Execution Plan”.
Have also found out that the index has to be created only when the data exists in the temp table.
One way though i have found is that when u enforce the unique or primary key on creation of the temp tables then the “Index Seek” is used in the execution plan.
I would really like to have you an article featured on this topic.
And yes, your blog is awesome. I have learnt so much from your articles.
Thanks.