Menu
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?
708
Unique keys are indexes. If your values are guaranteed to be unique, this is the best choice. Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column.
We know that the unique constraint in SQL Server creates a unique SQL Server index as well. SQL Server allows us to disable an index as well without dropping it.
Provided that the data in each column is unique, you can create both a unique clustered index and multiple unique nonclustered indexes on the same table. Unique indexes ensure the data integrity of the defined columns.
A unique index ensures that the values in the index key columns are unique. A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).
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).
62
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
