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 (http://blog.SQLAuthority.com)