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

tsql indexing temp-tables
Manu picture Manu · Sep 18, 2008 · Viewed 16.5k times · Source

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

Answer

sdkpoly picture sdkpoly · Sep 18, 2008

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

Related questions

Is it possible to add index to a temp table? And what's the difference between create #t and declare @t

I need to do a very complex query. At one point, this query must have a join to a view that cannot be indexed unfortunately. This view is also a complex view joining big tables. View's output can be simplified …

Read More
Best use of indices on temporary tables in T-SQL

If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this: "the …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More