Does unique on a temporary table column create an index?

Question

I have a temporary table that I'm using and I have a unique column, example:myID INT UNIQUE

Would this create an index on that column? Or do I have to explicitly create the index?

Answer 1

Yes, SQL server will automatically create an index for each additional unique constraint.

http://msdn.microsoft.com/en-us/library/ms177420.aspx

"The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint....Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint."

This is true for temporary tables, which I just verified by testing.

Answer 2

Yes, it does - see this article here:

UNIQUE Constraints vs. UNIQUE Indexes
Many database administrators ask about the difference between a UNIQUE constraint and a UNIQUE index. While you may use different Transact-SQL commands to create them (ALTER TABLE…ADD CONSTRAINT for constraints and CREATE UNIQUE INDEX for indexes), they have the same effect, for the most part. In fact, when you create a UNIQUE constraint, it actually creates a UNIQUE index on the table.