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.