SQL Server - Unique Indexes
In case of unique indexes there are no duplicate values in index keys and hence every row in the table is in a way unique. In other words a unique index ensures that the values contained within the unique index columns will appear only once within the table, including a value of NULL.
A primary key or a unique constraint automatically creates a unique index. When primary key constraint is defined on one or more columns, SQL Server automatically creates a unique clustered index, if a clustered index does not already exists on the table. Similarly when you define a unique constraint, SQL Server automatically creates a unique non-clustered index.
Syntax for creating a Unique Index
For more on SQL Server Unique Indexes below books may be helpful:
1. Beginning SQL Server 2012 for Developers by Robin Dewson.
2. Pro SQL Server 2005 Database Design and Optimization by Kurt Windisch, Kevin Kline, Louis Davidson.
In case of unique indexes there are no duplicate values in index keys and hence every row in the table is in a way unique. In other words a unique index ensures that the values contained within the unique index columns will appear only once within the table, including a value of NULL.
A primary key or a unique constraint automatically creates a unique index. When primary key constraint is defined on one or more columns, SQL Server automatically creates a unique clustered index, if a clustered index does not already exists on the table. Similarly when you define a unique constraint, SQL Server automatically creates a unique non-clustered index.
Syntax for creating a Unique Index
CREATE UNIQUE INDEX index_name ON table_name (column_name)As far as the performance goes it is best to use unique indexes whenever possible as they enhance the SQL Server optimizer’s chances of predicting how many rows will be returned from a query that uses the index. If the index is unique, the maximum number of rows that can be returned from a query that requires equality is one.
For more on SQL Server Unique Indexes below books may be helpful:
1. Beginning SQL Server 2012 for Developers by Robin Dewson.
2. Pro SQL Server 2005 Database Design and Optimization by Kurt Windisch, Kevin Kline, Louis Davidson.