Unique key vs. unique index on SQL Server 2008

问题:

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 ?

回答1:

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).

回答2:

One additional thing to mention, is that if you create index, you can specify included columns , this can help your sql code to work faster if there is some searching by country_name.

CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_Bar
ON dbo.foo (
    bar
)
INCLUDE (foo_other_column)
GO

SELECT foo_other_column FROM Foo WHERE bar = 'test'

SQL server will store "foo_other_column" in index itself. In case of unique constraint it will first find index of 'test', then will search for row in foo table and only there it will take "foo_other_column".

回答3:

If you are using SqlMetal.exe to output DBML or LinqToSql entities:

• If a foreign key uses a unique key, then you will get an association as expected.
• If a foreign key uses a unique index, it won't show up.

The reason is in the implementation of SqlMetal. It queries the database information schema, specifically key column usage. Unique keys are represented there, but unique indexes are not.

SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

回答4:

Other than the excellent answers above, I'd add my 2 cents here.

Unique key is a constraint and it uses unique index to enforce itself. Just as primary key is usually enforced by a clustered unique index. Logically speaking, a constraint and an index are two different things. But in RDBMS, a constraint can be physically implemented through an index.

If a table is created with an unique constraint in sql server, you will see both a constraint object and a unique index

create table dbo.t (id  int constraint my_unique_constraint unique (id));

select [Constraint]=name from sys.key_constraints 
where parent_object_id = object_id('dbo.t');

select name, index_id, type_desc from sys.indexes
where object_id = object_id('dbo.t')
and index_id > 0;

we will get the following (a constraint and an index)

However, if we do not create a constraint but just a unique index as the following

create table dbo.t2 (id int );
create unique index my_unique_constraint on dbo.t2 (id);

select [Constraint]=name from sys.key_constraints 
where parent_object_id = object_id('dbo.t2');

select name, index_id, type_desc from sys.indexes
where object_id = object_id('dbo.t2')
and index_id > 0

You will see there is NO constraint object created (only an index created).

From "theoretical" perspective, in SQL Server, a constraint is an object with object_id value and is schema-bound, while an index is not an object and has no object_id value and no schema related.

回答5:

There is no difference between unique index or a unique constraint and also there is no performance difference. However there are some differences for creation where some index creation options are not available for unique constraints.

回答6:

A 3rd Option to enforce Uniqueness is using a Filtered Unique-Index to allow a Nullable Unique Index.
This will not work with Unique-Constraints.
For example, say you have a column where you only want to allow unique values,
   but still want to support multiple NULL values when they do not exist.
Only a Filtered Unique-Index would work:

CREATE UNIQUE NONCLUSTERED INDEX [UF_Employee_UserID] ON [dbo].[Employee]
(
    [UserID] ASC--Not all Employees have a UserID to log into the System.
)
WHERE ([UserID] IS NOT NULL)--Enforce Uniqueness when not null.

Right now, you still cannot create a Filtered-Index in SSMS while editing a Table using the GUI.
However, you can close all of your open Table Designers, then open up the Properties of the Index itself in the Object Explorer, if you wanted to go through the GUI instead of creating the Index by hand (like above).