Unique key vs. unique index on SQL Server 2008

sql sql-server sql-server-2008 tsql sql-server-2008-r2
john Gu picture john Gu · Apr 22, 2012 · Viewed 32.6k times · Source

I have a table called countries and I define the country_name column to be unique by creating a “Index/Key” of type “Unique Key” on SQL Server 2008 R2.

But I have the following questions:

  1. will creating “Index/Key” of type “Unique Key” automatically create a non-clustered index on this column?
  2. if I change the type from being “Unique Key” to "Index" and I keep the IsUnique value to be "Yes",, then will there be any differences ?
  3. so why there are two options “Unique Key” and "Index" I think the two are the same ?

Answer

Aaron Bertrand picture Aaron Bertrand · Apr 22, 2012

A unique constraint is implemented behind the scenes as a unique index, so it doesn't really matter how you specify it. I tend to implement it simply as:

ALTER TABLE dbo.foo ADD CONSTRAINT UQ_bar UNIQUE(bar);

Some people create a unique index instead, e.g.

CREATE UNIQUE INDEX IX_UQ_Bar ON dbo.foo(bar);

The difference is in the intent - if you are creating the constraint to enforce uniqueness/business rules, you create a constraint, if you are doing so to assist query performance, it might be more logical to create a unique index. Again, under the covers it's the same implementation, but the road you take to get there may help document your intent.

I think there are multiple options to adhere to both previous Sybase functionality as well as to adhere to the ANSI standard (even though unique constraints don't adhere to the standard 100%, since they only allow one NULL value - a unique index, on the other hand, can work around this by adding a WHERE clause (WHERE col IS NOT NULL) on SQL Server 2008 and greater).

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Using IF ELSE statement based on Count to execute different Insert statements

While I am searching through my database, I run an INSERT statement if I find that a particular item does not exist, and I run a different INSERT statement if I find one or more of this item. I am …

Read More
T-SQL split string based on delimiter

I have some data that I would like to split based on a delimiter that may or may not exist. Example data: John/Smith Jane/Doe Steve Bob/Johnson I am using the following code to split this data into …

Read More