Dropping indexes of global temp table in SQL Server

Question

Possible Duplicate:
TSQL: Are indexes on temporary tables deleted when the table is deleted?

How do you manually drop an index of a global temp table in SQL Server?

EDIT: Do you even need to if you manually drop the table?

Answer 1

You do not need to drop the index on a temp table if you are going to delete the temp table itself. It will happen automatically when you drop the table.

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

Answer 2

The global temporary table will be destroyed with its indexes when all sessions referencing the table will end, or when you will drop the table manually, however, you still can drop the index by hand if you need to.

The syntax is

drop index ##MyTempTable.MyIndex

or:

drop index MyIndex on ##MyTempTable

Answer 3

This worked for me in creating and then dropping the index.

CREATE TABLE ##TempTable (Col1 int)

CREATE INDEX IDX on ##TempTable
(Col1)

DROP INDEX IDX ON ##TempTable