SQL Server UNIQUE indexes are a type of index that enforce the uniqueness of the indexed columns. A unique index ensures that no two rows in a table have duplicate values in the indexed columns. This makes them useful for enforcing business rules, such as a Social Security number or Employee ID, that must be unique for each row in a table.

A table can have multiple unique indexes, but each one must have a unique set of indexed columns. When a new row is inserted or an existing row is updated, the indexed columns of the row are checked against the existing indexed columns in the table to ensure that the values are unique. If a duplicate value is found, the operation will be rolled back and an error message will be returned.

Unique indexes can be created by using the CREATE UNIQUE INDEX statement, specifying the table name and the columns that will be included in the index. Additionally, a unique constraint can be added when creating a table using the UNIQUE keyword in the column definition.

Clustered indexes are also unique by default, as a table can have only one clustered index and it creates a unique constraint on the key column or columns.

Example

Here's an example of creating a UNIQUE index in SQL Server:

CREATE UNIQUE INDEX idx_unique_email
ON users (email);

This statement creates a unique index called idx_unique_email on the email column in the users table. This means that no two rows can have the same value for the email column. If you try to insert a row with a duplicate email value, the insertion will fail and an error will be thrown.

You can also create a unique index on multiple columns by listing them after the ON keyword:

CREATE UNIQUE INDEX idx_unique_name_and_age
ON users (name, age);

This creates a unique index called idx_unique_name_and_age on both the name and age columns in the users table. This means that no two rows can have the same combination of values for these two columns.