[dba-SQLServer] Unique indexes vs. constraints

In BOL, the following statement is made about unique constraints:

"SQL Server automatically creates a UNIQUE index to enforce the
uniqueness requirement of the UNIQUE constraint. Therefore, if an
attempt to insert a duplicate row is made, SQL Server returns an error
message that says the UNIQUE constraint has been violated and does not
add the row to the table. Unless a clustered index is explicitly
specified, a unique, nonclustered index is created by default to enforce
the UNIQUE constraint."

Now, under unique indexes, I am not sure why it make the following
statement about using PRIMARY KEY or UNIQUE constraint instead of UNIQUE
index:

"Specifying a unique index makes sense only when uniqueness is a
characteristic of the data itself. If uniqueness must be enforced to
ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the
column rather than a unique index. For example, if you plan to query
frequently on the Social Security number (ssn) column in the employee
table (in which the primary key is emp_id), and you want to ensure that
Social Security numbers are unique, create a UNIQUE constraint on ssn.
If the user enters the same Social Security number for more than one
employee, an error is displayed."

Hope this clarifies.


Ken Stoker
Technology Commercialization
Information Systems Administrator
PH: (509) 375-3758
FAX: (509) 375-6731
E-mail:  Kenneth.Stoker at pnl.gov 


-----Original Message-----
From: Mark Whittinghill [mailto:mwhittinghill at symphonyinfo.com] 
Sent: Monday, March 08, 2004 7:46 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Unique indexes vs. constraints


What's the value of using a unique constraint vs. a unique index?  Is it
that you would use an index if you also wanted to sort or search on that
field, and a constraint if you just wanted to enforce uniqueness without
the overhead of an index?  Or is there more to it than that?


Mark Whittinghill
Symphony Information Services
763-391-7400
mwhittinghill at symphonyinfo.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com