none
Difference Between Unique Index and Unique Key

    Question

  • What is the difference, if any, between an index created with IsUnique set to yes, and a unique key, using SQL Server 2008 R2 Management Studio?  Is there a reason why one is given a choice?

    This question was asked in a previous forum, but I did not understand the answer.  I don't want to make any sophmoric errors in setting up a particular table in my database.

    Details:

    The table in question has a primary key that is composed of a person's GUID and an email GUID (the former linking to a person table and the latter set by Newid()).  So a person can have multiple emails.

    The third column in the table is the email address itself (using varchar(256), despite real limit of 254 -- any comments on that welcome as well).  This column should be unique.  I want to index it and keep it unique.  So do I use a unique index, or a unique key, on that field?

    Thank you for any help you can give me.


    Jack
    Friday, April 01, 2011 9:05 PM

Answers

All replies

  • Check this older thread on this same topic.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, April 01, 2011 9:41 PM
  • Hi Jack,

    For all practical purposes, there is no difference between those two. Internally there are the same under the covers.

    Here is a good read on the topic.

    http://msdn.microsoft.com/en-us/library/aa224827(v=sql.80).aspx


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Marked as answer by Jack Herr Friday, April 01, 2011 10:16 PM
    Friday, April 01, 2011 9:44 PM
  • Thank you both for your responses.  Because every source I consult attempts to explain the differences between unique constraints and unique indexes (correct plural?), with no mention of unique keys (that are not also the primary key as a clustered unique index), I will opt for the unique index.  I understand that creating a unique constraint will also create clarity of purpose, and will do that as well.

    Thanks.


    Jack
    Friday, April 01, 2011 10:21 PM