Is the Sql Server Unique Key also an Index?

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?

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

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