Menu
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
411
Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible. All indexes and triggers associated with a table are dropped.
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used. There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability.
You can define indexes on temporary tables. In many cases, these indexes can improve the performance of queries that use tempdb. The optimizer uses these indexes just like indexes on ordinary user tables.
All rows from the table are dropped. All table indexes and domain indexes are dropped, as well as any triggers defined on the table, regardless of who created them or whose schema contains them. If table is partitioned, then any corresponding local index partitions are also dropped.
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."
132
It will be removed automatically, as there is nothing left to index. Think of it as a child object in this respect.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
