Search code examples
sqlsql-serverindexingtemp-tables

Dropping a global temp table does not drop its index

Problem summary:

I created a global temp table and then I created a clustered index (added a primary key clustered constraint) on the table. I dropped the table assuming that it will also drop the index. I then recreated the table with the same name without problems. Then, when I tried to recreate the same index with the same name I get the error: 

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.##MyTempTable' and the index name 'PK_TempSampleID'.

Problem details:

I need to create a global temp table and load records into this table in a particular order using:

SELECT FROM SomeTable INTO ##MyTempTable WHERE SomeCondition

ORDER BY does not do the job here, so I created a clustered index (primary key clustered constraint). This did the trick and my records were ordered by the respective field.

However, I encountered a different problem: I dropped the table and recreated it without problems but when I tried recreating the index I got the above mentioned error.

I tried dropping the index using:

DROP INDEX PK_TempSampleID ON ##MyTempTable

And I got this error message:

Cannot drop the index '##TempFormattedSnapshot.PK_TempSampleID', because it does not exist or you do not have permission.

I researched posts on this and other forums and all seem to advise that when dropping a temp table the index on that table is also dropped. My experience shows me otherwise.

Solution

  • The error message

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.##MyTempTable' and the index name 'PK_TempSampleID'.

    means that there are duplicate values in the column(s) for which you are trying to create a unique index. In other words, not all values are unique.

    It has nothing to do with the previously existed index of the same name.

    When you drop a table, all indexes on that table are dropped as well.

    You have another problem, which is out of the scope of this question. You said "load records into this table in a particular order". You'd better ask another question explaining what you are trying to achieve, because there is no such thing as "inserting rows into a table in a particular order". The only thing that you can do is generate IDENTITY values in a particular order when inserting rows.

    Anyway, this is not the point of this question.