none
What 's difference between Unique key and unique index

Answers

  • You have it.

     

    From Books Online:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0963cbbd-6f08-4968-b4ce-b00d023372a4.htm

     

    Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE 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. (emphasis added.)

     

    Wednesday, November 14, 2007 3:40 PM

All replies

  • Unique Key

    http://www.answers.com/topic/unique-key

     

     

    From BOL

     

    UNIQUE constraints

     

    You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

     

    Unique Index

     

    A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. For example, if you want to make sure that the values in the NationalIDNumber column in the HumanResources.Employee table are unique, when the primary key is EmployeeID, create a UNIQUE constraint on the NationalIDNumber column. If the user tries to enter the same value in that column for more than one employee, an error message is displayed and the duplicate value is not entered.

     

     

    Madhu 

    Tuesday, November 13, 2007 3:20 AM
  • Effectively, there is no difference.

     

    A Unique CONSTRAINT is a declarative element of data definition, and requires an Unique Index in order to work efficently.

     

    A Unique Index effectively creates a constraint.

     

    Tuesday, November 13, 2007 7:16 AM
  • Then could I understand in this way?

    Unique Key: which is a constraint to garantee no dup records occur in this specified column.

    Unique index: it will index on this column besides garanteeing no dup records occur in this specified column

    Wednesday, November 14, 2007 9:36 AM
  • It may be like

     

    Unique Key is internally implemented using Unique Index.

     

    Arnie will make the statement more clear

     

    Madhu

     

    Wednesday, November 14, 2007 11:52 AM
  • You have it.

     

    From Books Online:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0963cbbd-6f08-4968-b4ce-b00d023372a4.htm

     

    Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE 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. (emphasis added.)

     

    Wednesday, November 14, 2007 3:40 PM
  • I saw this quote earlier, but while good, the emphasised part is very unclear. What is meant by an data integrity which is objective, what is meant by objective of the index. What does objective mean in this sentence, can you give a clearer example. Thanks.

    Wednesday, August 01, 2012 6:30 PM
  • Unique key is for Data integrity, nevertheless uniqe index is for performance.
    Monday, September 24, 2012 2:08 AM
  • Functionally, enforcing uniqueness & performance, they are equivalent. UNIQUE KEY is part of the DDL. Index is a kind of second-class citizen, not even in sys.objects.

    I prefer to use UNIQUE KEY for a NATURAL KEY in the table.

    Differences between UNIQUE KEY and UNIQUE INDEX:

    1. UNIQUE KEY is a database object; UNIQUE KEY constraints are in sys.objects.

    2. UNIQUE KEY is in ANSI SQL.

    3. UNIQUE can be used in CREATE TABLE.

    CREATE TABLE Celebrity (
    CelebrityID INT IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(64) UNIQUE);

    4. UNIQUE KEY can be FOREIGN KEY referenced from another table just like the PRIMARY KEY.

    CREATE TABLE Movie(
    MovieID INT IDENTITY(1,1) PRIMARY KEY,
    CelebrityID  nvarchar(64) REFERENCES Celebrity(Name),
    Title nvarchar(256));

    5. Most database modelling software recognizes UNIQUE KEY:

    6. UNIQUE KEY is listed unders Keys in Object Explorer with a blue key icon.

    7. Not all index creation options are available when creating a UNIQUE KEY constraint.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012





    • Edited by Kalman Toth Tuesday, September 25, 2012 12:02 AM
    Monday, September 24, 2012 8:26 AM