Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Does unique on a temporary table column create an index?

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?

like image 570
Bmw Avatar asked Feb 28 '23 08:02


2 Answers

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


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

like image 91
Jeffrey L Whitledge Avatar answered Mar 06 '23 21:03

Jeffrey L Whitledge

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.

like image 43
marc_s Avatar answered Mar 06 '23 21:03