What is the difference between the unique key constraint of SQLServer and the unique index ?, Sqlserver Index

What is the difference between the unique key constraint of SQLServer and the unique index ?, Sqlserver Index

I used to want to know what the difference was, but I forgot it because we seldom use the unique key constraint. A few days ago, my colleague gave me a script to constrain the uniqueness of a field and use the unique key constraint. There seems to be a difference, and no big difference is found! In fact, there is no big difference. Let's test it.

USE [DemoDB]GOCREATE TABLE [dbo].[TableUniqueKey](id int not null,name varchar(20) null)GOCREATE TABLE [dbo].[TableUniqueIndex](id int not null,name varchar(20) 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

Create a unique key and unique index for two table shards. The duplicate value of the field name is null.

An error occurred while creating a unique key constraint and creating a unique index!

/* Create a unique key constraint and create a unique index */-- create a unique key constraint alter table [dbo]. [TableUniqueKey] add constraint [IX_TableUniqueKey_name] UNIQUE ([name] ASC) -- default non-clustered index goalter table [dbo]. [TableUniqueKey] add constraint [IX_TableUniqueKey_name] unique nonclustered ([name] ASC) GO -- CREATE a unique index create unique nonclustered index [IX_UniqueIndex_name] ON [dbo]. [TableUniqueIndex] ([name] ASC) GO

Msg 1505, Level 16, State 1, Line 1
The create unique index statement terminated because a duplicate key was found for the object name 'dbo. tableuniquekey' and the index name 'ix _ TableUniqueKey_name '. the duplicate key value is (<NULL> ).
Msg 1750, Level 16, State 0, Line 1
Cocould not create constraint. See previous errors.
The statement has been terminated.

Msg 1505, Level 16, State 1, Line 1
The create unique index statement terminated because a duplicate key was 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.

We can see that duplicate values are displayed. The duplicate value is NULL. Delete duplicate values.

DELETE FROM [dbo].[TableUniqueKey] WHERE ID = 3DELETE FROM [dbo].[TableUniqueIndex] WHERE ID = 3

Create a unique key constraint:

-- Create a 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('TableUniqueKey')

Create a unique key constraint, create a unique non-clustered index with the same name, and create a statistical information with the same name. The unique key constraint depends on the unique index.

An error occurred while deleting the index generated by the unique key!

-- An error occurred while deleting the index! Drop index [IX_TableUniqueKey_name] ON [dbo]. [TableUniqueKey] GO

Msg 3723, Level 16, State 5, Line 1
An explicit drop index is not allowed on index 'dbo. TableUniqueKey. IX_TableUniqueKey_name '. It is being used for unique key constraint enforcement.

Delete the Table constraint correctly.

-- Delete constraints. Correct! Alter table [dbo]. [TableUniqueKey] drop constraint [IX_TableUniqueKey_name] GO

Create a unique index for another table.

-- Create unique index create unique nonclustered index [IX_UniqueIndex_name] ON [dbo]. [TableUniqueIndex] ([name] ASC) GO

Create a unique index and create statistics with the same name

-- Delete the index! Drop index [IX_UniqueIndex_name] ON [dbo]. [TableUniqueIndex] GO

Re-create now:

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

Comparison 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, but a constraint of UNIQUE_CONSTRAINT. Their indexes have uniqueness constraints.

In addition, you can also check their differences through the following:

EXEC sp_helpconstraint 'TableUniqueKey' EXEC sp_helpconstraint 'TableUniqueIndex' EXEC sp_helpindex 'TableUniqueKey' EXEC sp_helpindex 'TableUniqueIndex' --EXEC sp_help 'TableUniqueKey' --EXEC sp_help 'TableUniqueIndex' 

In the index description, the unique key is more than the unique index.Unique key.

-- Unique key error: EXEC sp_help [distinct] EXEC sp_helpindex [distinct] EXEC sp_helpconstraint [distinct] -- unique index error EXEC sp_help [IX_UniqueIndex_name] EXEC sp_helpindex [IX_UniqueIndex_name] EXEC limit [IX_UniqueIndex_name]

As shown above, the only difference is that the unique key has a constraint called "unique key" than the unique index.

Disable index now:

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

Data can be inserted normally, and the constraints or indexes are disabled. Is the index disabled for the unique key constraint?

The CnstIsDisabled parameter of the ObjectProperty () function can be used to check whether the constraint is disabled.

SELECT ObjectProperty(object_id('IX_TableUniqueKey_name'),'CnstIsDisabled')

The result is 0, indicating that the constraint is not disabled. That is to say, the index is disabled. In the unique key constraint, Uniqueness depends on the unique index created by default!

Re-indexing now:

-- DELETE duplicate delete from [dbo]. [TableUniqueKey] where id = 3 delete from [dbo]. [TableUniqueIndex] where id = 3/* changes to the INDEX */-- re-creates the index alter index [IX_TableUniqueKey_name] ON [dbo]. [TableUniqueKey] rebuildalter index [IX_UniqueIndex_name] ON [dbo]. [TableUniqueIndex] REBUILD -- change part of the 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

You can change the parameters of the two indexes. Use the window to open the view. Some indexes with unique key constraints cannot be changed.

An index with a unique key constraint cannot use too many index parameters as a normal index, because the unique key constraint is the same as its index. More parameters can be set for the unique index created separately, such as PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY, DROP_EXISTING, STATISTICS_NORECOMPUTE, and SORT_IN_TEMPDB.

In general, in factUnique key constraintAndUnique IndexFeatures are the same: "uniqueness" + "Index"

Unique key constraintAs a unique constraint (such as primary key constraint, unique key constraint, check constraint, and foreign key constraint), it is managed as a constraint. at the same time, the unique non-clustered index is automatically created, which provides the performance and some functions of the index. in fact, the unique key constraint is restricted by the unique index.

Unique IndexIt is an index. It checks the uniqueness of a field and can set various parameters, which is very flexible.

Which of the following statements is better when we create the uniqueness of a column? (Personal understanding)

Unique key constraintThere must be constraints in the table,Unique key constraintThe index exists in a partition and cannot be changed as the index does. Because indexes can be changed at any time (and will not be changed frequently), I suggest using indexes.Unique IndexMore flexible. You have to manage indexes as well as manage indexes as management constraints. However, for some high availability scenarios, you must also check whether the index exists elsewhere.

For more information, see Unique Constraints and Unique Indexes.