Does a unique table table column create an index?

Question

I have a temporary table that I use and I have a unique column, for example: myID INT UNIQUE

Will this create an index in this column? Or do I need to explicitly create an 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 ensure that the UNIQUE constraint is unique ... If a clustered index is not explicitly specified, a unique non-clustered index is created by default to enforce the UNIQUE constraint."

This is true for temporary tables that I just checked by testing.

Answer 2

Yes, it is - see this article here :

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