SQL SERVER – SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2

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)

3 thoughts on “SQL SERVER – SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2

  1. 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..


  2. 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.



  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #029 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s