CODEWITHSUNDEEP
t-sqlindexingtemp-tables
Manu
Manu

Reputation: 29143

Are indexes on temporary tables deleted when the table is deleted?

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

Upvotes: 22

Views: 19792

Answers (3)

James Boother
James Boother

Reputation: 42763

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.

Upvotes: 2

sdkpoly
sdkpoly

Reputation: 321

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

Upvotes: 25

Nick Craver
Nick Craver

Reputation: 630429

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

Upvotes: 6

Related Questions