Pages

Thursday, November 17, 2011

Unique Index in SQL Server 2005/2008

Introduction
An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.



Implementations
Unique indexes are implemented in the following ways:

  • PRIMARY KEY or UNIQUE constraint : When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values. When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.
  • Index independent of a constraint: Multiple unique nonclustered indexes can be defined on a table
  • Indexed view: To create an indexed view, a unique clustered index is defined on one or more view columns. The view is executed (materialized) and the result set is stored in the leaf level of the index in the same way table data is stored in a clustered index. 

Handling NULL Values
you cannot create a unique index, or UNIQUE constraint, if the key values are NULL in more than one row. Select columns that are defined as NOT NULL when you choose columns for a unique index or unique constraint.


Syntax:
CREATE UNIQUE CLUSTERED INDEX IX_Employee
ON Employee
([Id][NameDESC)
GO

No comments: