Is Sql Server Unique Key also an index?

Question

I have a column in a table (e.g. UserName) that I want to make sure is unique. Therefore, I create a unique key for this column and call it IX_Users_UserName.

Now, if I search a lot for users based on their username, I want to make sure there is an index for this field.

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

Answer 1

Unique key: a unique key ensures the uniqueness of the column on which they are defined. The unique key creates a non-clustered index in the column. The unique key allows only one NULL value.

Modify the table to add a unique constraint to the column:

ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE (Name) GO

Source

Additional information from MSDN .

FWIW - if your restriction does not create an index, I would not call it IX_ , since it is usually assumed that it is associated with one (IX = Index).

Answer 2

Basically, in SQL Server, a unique constraint is actually implemented using a unique index.

The differences between the UNIQUE constraint and the UNIQUE INDEX are actually quite subtle. If you create a UNIQUE INDEX, you can refer to it in foreign key constraints from another table (does not work if you create a UNIQUE constraint ....).

And what's the difference? Good - a unique constraint is indeed more logical for a table - you want to express the intention that the contents of a given column (or group of columns) be unique.

A unique index (like most indexes) provides more detailed information about the behind-the-scenes implementation.

From my point of view, if you really have problems with this, I will always use UNIQUE INDEX - the advantage of partial restriction of referential integrity is quite acceptable and can be very useful in some cases. Functionally, in practice, there is no difference between using a unique constraint and a unique index.

Answer 3

The unique key is the index in which I suspect almost every database product. It must be, otherwise the database would be difficult to execute it: when you insert the value, the database should answer: "does this value already exist?" A good way to do this is to access the index.

I don’t have SQL Server in front of me to check, but I would be shocked if it weren’t.