What is the difference between SQL Server unique key constraints and unique indexes?

I used to want to know what the difference was, but it was forgotten because we seldom used a unique key constraint. It was not until a few days ago that my colleague gave me a script to constrain the uniqueness of a field with a unique key constraint. There seems to be a difference, and no big difference found! Actually, it's not much different, just test it out.

Use [Demodb]gocreate TABLE [dbo]. [Tableuniquekey] (id int not null,name varchar () null) Gocreate TABLE [dbo]. [Tableuniqueindex] (id int not null,name varchar () null) Goinsert into [dbo]. [Tableuniquekey] SELECT 1, ' KK ' Union allselect 2,null Union allselect 3,null Goinsert into [dbo]. [Tableuniqueindex] SELECT 1, ' KK ' Union allselect 2,null Union allselect 3,null GO

Creates a unique key and a unique index with two table tables, with duplicate values null in the field name.

Now create a unique key constraint and create a unique index, error!

/* Create unique key constraints and create unique indexes */--create unique KEY constraint alter TABLE [dbo]. [Tableuniquekey] ADD CONSTRAINT [Ix_tableuniquekey_name] UNIQUE ([name] ASC)--default nonclustered index goalter TABLE [dbo]. [Tableuniquekey] ADD CONSTRAINT [Ix_tableuniquekey_name] unique nonclustered ([name] ASC) go--creating unique index create unique nonclustered index [ix_ Uniqueindex_name] on [dbo]. [Tableuniqueindex] ([name] ASC) GO

MSG 1505, Level A, State 1, line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key is found for the object name ' dbo. Tableuniquekey ' and the index name ' Ix_tableuniquekey_name '. The duplicate key value is (<NULL>).
MSG 1750, level A, state 0, line 1
Could not create constraint. See previous errors.
The statement has been terminated.

MSG 1505, Level A, State 1, line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key is found for the object name ' dbo. Tableuniqueindex ' and the index name ' Ix_uniqueindex_name '. The duplicate key value is (<NULL>).
The statement has been terminated.

As you can see, you are prompted for duplicate values, and the duplicate values are NULL values. Now delete the duplicate values.

DELETE from [dbo]. [Tableuniquekey] WHERE ID = 3DELETE from [dbo]. [Tableuniqueindex] WHERE ID = 3

To create a unique key constraint:

--Create UNIQUE KEY constraint alter TABLE [dbo]. [Tableuniquekey] ADD CONSTRAINT [Ix_tableuniquekey_name] UNIQUE nonclustered ([name] ASC) GO

View related information:

SELECT * from sys.check_constraints WHERE parent_object_id = object_id (' tableuniquekey ') select * from Sys.key_ constraints where parent_object_id = object_id (' tableuniquekey ') SELECT * from sys.indexes WHERE object_id = object_id (' Ta Bleuniquekey ')

Creates a unique key constraint, creating a unique nonclustered index with the same name, and creating statistics with the same name; Unique key constraints are constrained by a unique index.

If the index generated by the unique key is deleted directly, the error!

--Delete the index, error! DROP INDEX [ix_tableuniquekey_name] on [dbo]. [Tableuniquekey] GO

MSG 3723, Level A, State 5, line 1
An explicit DROP INDEX is not allowed on INDEX ' dbo. Tableuniquekey.ix_tableuniquekey_name '. It is the being used for UNIQUE KEY constraint enforcement.

Delete the method correctly and delete the table constraint.

--Delete the constraint, right! ALTER TABLE [dbo]. [Tableuniquekey] DROP CONSTRAINT [Ix_tableuniquekey_name]go

Now create a unique index on the other table.

--Creating unique index create unique nonclustered index [ix_uniqueindex_name] on [dbo]. [Tableuniqueindex] ([name] ASC) GO

Create a unique index while creating statistics with the same name

--delete the index! DROP INDEX [ix_uniqueindex_name] on [dbo]. [Tableuniqueindex] GO

Now re-create:

ALTER TABLE [dbo]. [Tableuniquekey] ADD CONSTRAINT [Ix_tableuniquekey_name] unique nonclustered ([name] ASC) gocreate unique nonclustered INDEX [ix_ Uniqueindex_name] on [dbo]. [Tableuniqueindex] ([name] ASC) GO

Compare related information:

SELECT * from sys.check_constraints WHERE parent_object_id = object_id (' tableuniquekey ') select * from Sys.check_ constraints where parent_object_id = object_id (' tableuniqueindex ') SELECT * from Sys.key_constraints WHERE parent_object _id = object_id (' tableuniquekey ') SELECT * from sys.key_constraints WHERE parent_object_id = object_id (' Tableuniqueindex ') SELECT object_name (object_id), Name,index_id,type_desc,is_unique,is_unique_constraintfrom Sys.indexes WHERE object_name (object_id) in (' Tableuniquekey ', ' Tableuniqueindex ')

The unique key constraint [Tableuniquekey] is not a check constraint and is a constraint that is a unique_constraint. And their indexes are uniquely constrained.

In addition, they can be checked by the following differences:

The unique key is more than the unique index in the comparison index description.

--Unique key without error EXEC sp_help [ix_tableuniquekey_name]exec sp_helpindex [Ix_tableuniquekey_name]exec sp_helpconstraint [IX_ tableuniquekey_name]--Unique index error EXEC sp_help [ix_uniqueindex_name]exec sp_helpindex [Ix_uniqueindex_name]exec sp_ Helpconstraint [Ix_uniqueindex_name]

As you can see, the only difference is that a unique key is more than a unique index a constraint called "Unique key" .

Now disable indexing:

--Disable index/constraint (both can be disabled) ALTER Index [ix_tableuniquekey_name] on [dbo]. [Tableuniquekey] Disablealter INDEX [ix_uniqueindex_name] on [dbo]. [Tableuniqueindex] DISABLE

Insert duplicate data:

--Insert duplicate values, normal INSERT INTO [dbo]. [Tableuniquekey] SELECT 3,null INSERT into [dbo]. [Tableuniqueindex] SELECT 3,null

The data can be inserted normally, and the constraint or index is disabled. is the index disabled for a unique key constraint?

The parameter of the function objectproperty () cnstisdisabled can confirm whether the constraint is disabled.

SELECT objectproperty (object_id (' Ix_tableuniquekey_name '), ' cnstisdisabled ')

The result is 0, that is, the constraint is not disabled, that is, the index is disabled, and the uniqueness in the unique key constraint is dependent on the unique index created by default!

Now rebuild the index:

--delete duplicate delete from [dbo]. [Tableuniquekey] WHERE ID = 3DELETE from [dbo]. [Tableuniqueindex] WHERE ID = 3/* changes to the index */--rebuild the index by ALTER [ix_tableuniquekey_name] on [dbo]. [Tableuniquekey] Rebuildalter INDEX [ix_uniqueindex_name] on [dbo]. [Tableuniqueindex] rebuild--change the partial index parameter alter INDEX [IX_TABLEUNIQUEKEY_NAME] on [dbo]. [Tableuniquekey] SET (allow_row_locks = on) goalter INDEX [ix_uniqueindex_name] on [dbo]. [Tableuniqueindex] SET (allow_row_locks = ON) GO

Two kinds of indexes can actually change the parameters. With the window open view, the index of the unique key constraint cannot be changed.

The index of a unique key constraint cannot use too many index parameters like a normal index because the unique key constraint is the same as its index. A unique index created alone can set more parameters, such as Pad_index, FILLFACTOR, Ignore_dup_key, Drop_existing, Statistics_norecompute, and Sort_in_ Tempdb.

In general, the unique key constraint and unique indexing functionality are the same: "Uniqueness" + "index"

A unique key constraint is just a unique constraint (such as a PRIMARY key constraint, a unique key constraint, a check constraint, a foreign key constraint), Managed in a constrained form. But at the same time, it automatically creates a unique nonclustered index, with the performance and some functionality of the index. In fact, unique key constraints are constrained by unique indexes.

A unique index is an index that uniquely checks a field and can set various parameters for flexibility.

So when we create the uniqueness of a column, which is better to use? (Personal understanding)

Unique key constraints are bound to exist in a table, and the index of a unique key constraint exists in a partition and is not changed as if it were an index. Because indexes can be changed at any time (and will not be changed frequently), it is more flexible to index individual suggestions or unique indexes . Management constraints also have to manage the index, and the one that manages the index is fine. However, for some high availability, it is also important to note whether the index exists elsewhere.

More information: Unique Constraints and unique Indexes

What is the difference between SQL Server unique key constraints and unique indexes?