Is the Sql Server Unique Key also an Index?

Question

I've got a column in a table (eg. UserName) which I want to make sure is unique. So I create a unique key for that column and call it IX_Users_UserName.

Now, if I do lots of searching for users based on their username I want to make sure there is an index for that field.

Do I need to create a separate index, or is the unique key also considered an index, just like the primary key is a clustered unique key?

Smilacaceous

I feel the need to point out that a primary key is not automatically a clustered unique key. Your clustered index is not required to be on the primary key. – Corso

Also see #565395 – Cacophony

Answer 1

Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:

ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name) GO

Source

More information from MSDN.

FWIW -- if your constraint doesn't create an index, I would avoid naming it IX_ as that would typically be assumed to be associated with one (IX = Index).

Lett

Cheers :) perfect answer. I'll also reanme all my unique keys to IX and drop the single index i had on those fields (why double up ... that's bad!). Cheers! – Smilacaceous

I like to prefix my unique indexes distinctively from non-unique indexes: UI or UIX. UK is also acceptable but does tell this is an index. – Fibroid

Answer 2

Basically, in SQL Server, a unique constraint is indeed realized by means of a unique index.

The differences between a UNIQUE constraint and a UNIQUE INDEX are quite subtle, really. If you create a UNIQUE INDEX, you can reference that in a foreign key constraints from another table (doesn't work if you create a UNIQUE constraint....).

So what's the difference? Well - a unique constraint really is more of a logical thing on a table - you want to express the intent that the contents of a given column (or group of columns) is unique.

A unique index (like most indices) is more of a "behind-the-scenes" implementation detail.

From my point of view, unless you really have a problem with it, I'd always use a UNIQUE INDEX - the benefit of being part of a referential integrity constraint is quite valid and can be very useful in certain cases. Functionally, in practice, there's no difference between using a Unique Constraint vs. Unique Index, really.

Rambert

Answer 3

A unique key is an index in I suspect almost every database product. It has to be, otherwise the database would have a hard time enforcing it: when you insert a value, the database has to answer, "does that value already exist?" The sane way to do that is consult an index.

I don't have a SQL Server in front of me to test, but I'd be shocked if it didn't.

Gout