Are indexes on temporary tables deleted when the t

问题:

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

回答1:

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

回答2:

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

回答3:

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.