MS SQL Server 2012 - DBA Articles

Unique Index



SQL Server implicitly creates a unique Nonclustered Index, when creates table with Unique constraint. The index column(s) are equal to constraint column(s) and the index name is equal to constraint name. Explicitly, we can create more than one Unique Index, when the table structure does not have a Unique constraint. The following example describes to create a Unique Index on Department table. This index helps to maintain unique data values.

-- Example 150 --

 

USE EBusiness

GO      

 

-- Create Unique Index on Department Table --

 

CREATE UNIQUE INDEX IX_Department_Unique

          ON dbo.Department (Dept_Name)

GO


Query Output Screen



Note:

For indexing purposes, NULL values compare as equal. Therefore, 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.

* * * * *


Email Your Comment To AUTHOR