Is the Sql Server Unique Key also an Index?
Sql ServerIndexingUnique KeySql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
> 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
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 - Sql Server
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.
Solution 3 - Sql Server
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.