none
Why is Unique Constraint is prefferd over Unique Index?

Answers

  • In SQL Server, a unique constraint is implemented by creating a unique index. Therefore, there are no significant differences between creating a unqiue constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a unique constraint on the column when data integrity is the objective. This makes the objective of the index clear.

    In SQL Server 2000 and earlier, there are some index options that cannot be set when creating a unique constraint that can be set when creating a unique index using CREATE INDEX. For example, you can set the IGNORE_DUP_KEY option when creating a unique index using CREATE INDEX, but you cannot set that option when creating a unique constraint.  This limitation does not exist in SQL Server 2005 and the work around in SQL Server 2000 is to use the CREATE INDEX WITH DROP_EXISTING after creating the constraint to set any additional index options.

    Regards,

     

    Thursday, January 05, 2006 10:19 PM

All replies

  • In SQL Server, a unique constraint is implemented by creating a unique index. Therefore, there are no significant differences between creating a unqiue constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a unique constraint on the column when data integrity is the objective. This makes the objective of the index clear.

    In SQL Server 2000 and earlier, there are some index options that cannot be set when creating a unique constraint that can be set when creating a unique index using CREATE INDEX. For example, you can set the IGNORE_DUP_KEY option when creating a unique index using CREATE INDEX, but you cannot set that option when creating a unique constraint.  This limitation does not exist in SQL Server 2005 and the work around in SQL Server 2000 is to use the CREATE INDEX WITH DROP_EXISTING after creating the constraint to set any additional index options.

    Regards,

     

    Thursday, January 05, 2006 10:19 PM
  • I agree with previous poster that there is no significant difference between Unique constraint and Unique index created on single column. both that values will be unique in column and therefore easier for optimizer to chose the index operation like SEEK and SCAN.

     

    There is one more thing with a cluster index. Clustered Index needs to be unique whether we use UNIQUE option or not while creating index. If UNIQUE keyword has not been used then SQL Server create unique index anyway by adding a Unique Identifier column internaly in your table. that column will not be visible and can not be selected. but it will be used to make sure clustered index is unique. Most of the time clustered index is created on auto generated IDENTITY column without unique option. SQL Server will still create internal column for that index to make it unique even though we know that auto generated columns is always going to be unique. so always use UNIQUE while creating clustered index otherwise you going to end up with 32 bytes extra in your clustered key. that will increase the length of noncluster keys as well.

    Friday, August 17, 2007 3:47 PM
  •  

    I have done small excercise to check if clustered index can always be unique if defined without UNIQUE keyword and found it will not.

     

    Following example will explain that clustered index can accept duplicate data if UNIQUE is not defined.

     

    --Create Table

    CREATE TABLE Test

    (

    idTest INT

    ,TestName VARCHAR(200)

    ,TestDescription VARCHAR(MAX)

    )

    --Create clustered index without specifying UNIQUE key word

    CREATE CLUSTERED INDEX idx_Test ON Test (idTest ASC)

    --Insert duplicate data

    INSERT INTO Test SELECT 1, 'Test1', 'Testing Clustered Index'

    INSERT INTO Test SELECT 1, 'Test1', 'Testing Clustered Index'

    --No Error Message in result

    SELECT * FROM Test

    --Now try specifying UNIQUE key word

    DROP INDEX idx_Test ON Test

    TRUNCATE TABLE Test

    CREATE UNIQUE CLUSTERED INDEX idx_Test ON Test (idTest ASC)

    INSERT INTO Test SELECT 1, 'Test1', 'Testing Clustered Index'

    INSERT INTO Test SELECT 1, 'Test1', 'Testing Clustered Index'

    --Error Message

    --Msg 2601, Level 14, State 1, Line 1

    --Cannot insert duplicate key row in object 'dbo.Test' with unique index 'idx_Test'.

    --The statement has been terminated.

    Thursday, September 11, 2008 1:53 PM
  • When UNIQIUE is not specified for a clustered index, SQL Server prevents ensures each row is unique by adding a hidden column (called a uniqueifier) to the index key.  You cannot see the uniqueifier column (see this Books Online topic (http://msdn.microsoft.com/en-us/library/ms190639(SQL.90).aspx ) . That is why you're not getting error 2601 in your first test.

     

    If your objective is to ensure that the key columns of your clustered index are unique without the addition of the uniqueifier column, then you must specify the UNIQUE property.

     

    Hope that helps,

     

    Gail

     

    Thursday, September 11, 2008 7:57 PM