21

Would the following SQL remove also the index - or does it have to be removed separately?

CREATE TABLE #Tbl (field int)

CREATE NONCLUSTERED INDEX idx ON #Tbl (field)

DROP TABLE #Tbl
user2864740
  • 60,010
  • 15
  • 145
  • 220
Manu
  • 28,753
  • 28
  • 75
  • 83

3 Answers3

24

Yes they are. You can search in MSSQL help for CREATE INDEX article it is said there:

"Indexes can be created on a temporary table. When the table is dropped or the session ends, all indexes and triggers are dropped."

sdkpoly
  • 311
  • 1
  • 8
  • And obviously. If all the pages are torn out the book, the index is useless and should be torn out too :) – Reversed Engineer Jul 23 '15 at 09:30
  • That's what I thought, but the problem I'm having is that when I recreate the temp table and then attempt to recreate the index (clustered primary key constraint) I'm getting this error: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.##TempFormattedSnapshot' and the index name 'PK_TempSampleID'. The duplicate key value is (422097). – user2430797 Oct 14 '19 at 06:28
6

It will be removed automatically, as there is nothing left to index. Think of it as a child object in this respect.

Nick Craver
  • 623,446
  • 136
  • 1,297
  • 1,155
2

The drop table will remove the index. Drop Index takes the index name and the table name.

In this case would be DROP INDEX idc ON #tbl

which can be called if you want to drop the index but leave the table.

James Boother
  • 41,623
  • 1
  • 17
  • 5