Is the Sql Server Unique Key also an Index?

Question

Hi folks,

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?

Thanks :)

Answer 1

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.

Answer 2

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

Answer 3

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. Funtionally, in practice, there's no difference between using a Unique Constraint vs. Unique Index, really.