This article is the part of series Mastering Index, in this particular article we will be exploring Unique Index. Take a look at the content of this series -
Mastering Index
One of the most interesting and talked about topic in database field is Index. I am writing this Series of article to cover all the different types of index available in SQL Server. I will be writing this series in two categories Index and Special Index. Please find the List of topics below, which I will covering –
Mastering Index
1. Clustered Index
2. Nonclustered Index
3. Unique Index
4. Index with included columns
5. Index on computed columns
6. Filtered Index
Special Index
1. XML Index
2. Full Text Index
3. Spatial Index
4. Columnstore Index
Unique Index
You may have guessed from the name, unique index guarantees the uniqueness of the index key.
You can create unique index by using the unique constraint or creating the unique index. By implementation and usage SQL Server doesn’t differentiate in them. Data validation is done in the same way and query optimizer treats them in the same way. You can create clustered or nonclustered unique index on the table. You have option to create a clustered unique index and multiple nonclustered unique indexes as per your business logic needs.
We cannot create unique index if the column contains duplicate data or more than one null. In the same way we cannot create unique index on multiple columns if it contains the duplicate combination of values or these columns must have unique combination of values.
When we create a unique index, we can set an option to ignore duplicate keys. When we set this to Yes and try to add duplicate index key values (using insert statement), duplicate key rows is not added. If we set this option to No, full insert operation fails and the transaction is rolled back. We will be testing this scenario later in this article.
Pros and Cons of Unique Index
Let’s start with Pros
1. Unique index guarantees the data integrity of the defined columns
2. Unique index like other index helps query optimizer to generate better execution plans
3. Multi column unique index guarantees the unique combination of column values
4. As per need you can create one clustered unique index or multiple nonclustered index if your table has unique data values in index key.
Cons
Since unique index is a variant of index, it has all the cons of an index. Unique index require memory for saving index structure and CPU for creating and maintaining the index. I have explained this in my previous articles of this series and I would recommend reading them. You can find the links to related article at the bottom of this article.
Let’s create a table for our unique index creation and Testing. Here is the script for creating the table and inserting few records for testing. I am keeping few records and maintaining the sequence, so it will be easy to observe the changes.
CREATE TABLE [dbo].[Test1](
[ID] [int] NULL,
[Name] [nchar](10) NULL,
[Address] [nchar](10) NULL
) ON [PRIMARY]
GO
Insert into [AdventureWorks2014].[dbo].[Test1] values (1,'Dev1','Add1')
Insert into [AdventureWorks2014].[dbo].[Test1] values (2,'Dev2','Add2')
Insert into [AdventureWorks2014].[dbo].[Test1] values (3,'Dev3','Add3')
Insert into [AdventureWorks2014].[dbo].[Test1] values (4,'Dev4','Add4')
Insert into [AdventureWorks2014].[dbo].[Test1] values (5,'Dev5','Add5')
SELECT [ID]
,[Name]
,[Address]
ID
|
Name
|
Address
|
1
|
Dev1
|
Add1
|
2
|
Dev2
|
Add2
|
3
|
Dev3
|
Add3
|
4
|
Dev4
|
Add4
|
5
|
Dev5
|
Add5
|
Once the test table is ready, let’s create Unique index with ignore duplicate key set to ON and see how it works.
CREATE UNIQUE INDEX Unique_Testing
ON [dbo].[Test1](Name,Address)
WITH (ignore_dup_key=ON)
GO
Now we will run the below script to insert some new records. As you can see in first insert statement Dev1 and Add1 values are already there in the table with ID 1. Since we have set the ignore duplicate key ON, it should insert the remaining records. Let’s run it and see.
BEGIN TRANSACTION;
BEGIN TRY
Insert into [AdventureWorks2014].[dbo].[Test1] values (6,'Dev1','Add1')
Insert into [AdventureWorks2014].[dbo].[Test1] values (7,'Dev7','Add7')
Insert into [AdventureWorks2014].[dbo].[Test1] values (8,'Dev8','Add8')
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
END CATCH;
When I ran this code I got below message, first insert statement is ignored and not being insert and remaining records had been inserted. Let’s cross check with select on Test1 table.
Duplicate key was ignored.
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
SELECT [ID]
,[Name]
,[Address]
FROM [AdventureWorks2014].[dbo].[Test1]
ID
|
Name
|
Address
|
1
|
Dev1
|
Add1
|
2
|
Dev2
|
Add2
|
3
|
Dev3
|
Add3
|
4
|
Dev4
|
Add4
|
5
|
Dev5
|
Add5
|
7
|
Dev7
|
Add7
|
8
|
Dev8
|
Add8
|
Yes, select statement confirms that remaining records has been inserted. Let’s close this tab and open new on to continue testing, we are closing current tab to make sure our transaction is closed to avoid deadlock.
Let’s drop the Unique index and recreate it with ignore duplicate key set to Off and see how it works with same scenario mentioned above.
Drop Index Unique_Testing on [AdventureWorks2014].[dbo].[Test1]
Go
CREATE UNIQUE INDEX Unique_Testing
ON [dbo].[Test1](Name,Address)
WITH (ignore_dup_key=OFF)
GO
As you can see we have same data Dev1 and Add1 in first statement which is already there in the table with ID 1, so it should show error and transaction should be rolled back because ignore duplicate is set to Off. Let’s run the below script and test it.
BEGIN TRANSACTION;
BEGIN TRY
Insert into [AdventureWorks2014].[dbo].[Test1] values (6,'Dev1','Add1')
Insert into [AdventureWorks2014].[dbo].[Test1] values (9,'Dev9','Add9')
Insert into [AdventureWorks2014].[dbo].[Test1] values (10,'Dev10','Add10')
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
END CATCH;
When I ran the script, I got the following error message. When I run the select for Test1 table output confirms that there is no record had been added. As you can see there is no record with ID 6,9 or 10, which proves that transaction has been rolled back.
Error Message
Cannot insert duplicate key row in object 'dbo.Test1' with unique index 'NonClusteredIndex_Demo_Unique_NC'. The duplicate key value is (Dev1 , Add1 ).
SELECT [ID]
,[Name]
,[Address]
FROM [AdventureWorks2014].[dbo].[Test1]
ID
|
Name
|
Address
|
1
|
Dev1
|
Add1
|
2
|
Dev2
|
Add2
|
3
|
Dev3
|
Add3
|
4
|
Dev4
|
Add4
|
5
|
Dev5
|
Add5
|
7
|
Dev7
|
Add7
|
8
|
Dev8
|
Add8
|
Now let’s see how we can create Clustered and Nonclustered index with unique constraint. Below is the script for creating from SQL or you can use management studio to do that.
/****** Object: Index [ClusteredIndex_Demo_Unique] Script Date: 10/14/2016 2:19:18 PM ******/
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex_Demo_Unique] ON [dbo].[Test1]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Step 1- Go to Object explorer > Test1>Index >Right click> Select Clustered Index.
Step 3- Refresh the Index folder to make sure index has been created.
USE [AdventureWorks2014]
GO
/****** Object: Index [NonClusteredIndex_Demo_Unique_NC] Script Date: 10/14/2016 2:19:44 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex_Demo_Unique_NC] ON [dbo].[Test1]
(
[ID] ASC,
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Step 1- Go to Object explorer > Test1>Index >Right click> Select Nonclustered Index.
Step 2 – In popup window, rename the index with some sensible name >select name and address column > check Unique checkbox >Ok
Step 3- Refresh the Index folder to make sure index has been created.
Deleting/ Dropping Index
You can use the Drop command or delete from GUI.
Select Index >Right Click>Select Delete>Click Ok on popup window
USE [AdventureWorks2014]
GO
DROP INDEX [IndexName] ON [TableName]
GO
Important DMVs
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats function
sys.dm_db_database_page_allocations function
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
Interview Questions
Q. What is the difference between primary key and unique key?
Q. What is the difference between unique index and clustered/ nonclustered index?
Q. what are the Pros and Cons of Unique index?
Q. What is the difference between Unique Index and Unique constraint?
Recommended Book for this Topic
