Unique Index in SQL

Unique Index in SQL Server

Unique Index in SQL Server

In this post I will explain all about Unique Index in SQL Server end to end with appropriate examples, Although this part is quite short. So let’s start –

Unique Index in SQL Server

Unique Index in SQL Server: An Unique index ensures that the index key columns don’t contain any duplicate values on which table or view it has defined. A unique index can be clustered or non clustered. Unique index may consist of one or many columns, if a unique index has one column then the values in this column will be unique. If a Unique index has multiple columns then the combination of values is unique. If we or any user try to INSERT or UPDATE any data into the unique index key column that causes the duplicate value then it will trough an error in result set.

Syntax:

CREATE UNIQUE INDEX index_name

ON table_name (column_list)

In the above syntax – we need to specify the name of the unique index after the CREATE UNIQUE INDEX keyword and then specify the name of the table to which the index associated and a list of columns that will be included in the index.

UNIQUE INDEX ON ONE COLUMN

Let’s go through some examples of using unique indexes, as we remember that in our last session we have created a table name – Items in which we have already created one clustered index and another one non clustered index. Now first we will run a query to get the information of one item which name is ‘MS SQL‘ and will see that by default query optimizer which index is using to scan the data –

Unique Index in SQL Server

As we can see above snapshot that query optimizer has taken all the clustered index in scan to find the required row. To speed up the retrieval of the query, we can add a non clustered index to the Item_Name column, however with the assumption that each name of the item will have a unique name, then we can create a unique index for the item_name column because the Items table already has some data then we need to check duplicate values first in the Item_Name column.

Syntax:

SELECT Item_Name, COUNT(Item_Name)
FROM Items
GROUP BY Item_Name
HAVING COUNT(Item_Name)>1

Message –

(0 row(s) affected)

As we can see that the query messaged (0 row(s) affected) and returned empty result set, that means there are no duplicate values in the Item_Name column, Therefore we can go ahead with the creation of a unique index for the Item_Name column of the Items table as mentioned below –

Syntax:

CREATE UNIQUE INDEX ix_Items_name
ON Items(Item_Name)

Now we can see that the query optimizer will use ix_Items_name index to seek a method to search for row by Item_Name going forward –

UNIQUE INDEX ON MULTIPLE COLUMN

Let’s go through some examples of using unique indexes, We will create a table named Student that has three columns for the training purpose –

Syntax:

CREATE TABLE Student (
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Class INT NOT NULL )

Now we will create a unique index that includes all columns ID, Name and Class columns

Syntax:

CREATE UNIQUE INDEX ix_Unique_Student
ON Student(ID,Name,Class)

Now we will insert one data into Student table.

Syntax:

INSERT INTO Student (ID, Name, Class)
VALUES (1,’Raghawendra’,10)

Data inserted successfully, now we will again insert another row into the Student table, note that the value 1 is repeated in the ID column but the combination of values in the column ID, Name and Class is not duplicate –

Syntax:

INSERT INTO Student (ID, Name, Class)
VALUES (1,’Raghawendra’,11)

Inserted successfully, Now we will again execute the same query that already exists into the Student table –

Syntax:

INSERT INTO Student (ID, Name, Class)
VALUES (1,’Raghawendra’,11)

Now we can see the Microsoft SQL Server thrown an error –

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Student’ with unique index ‘ix_Unique_Student’. The duplicate key value is (1, Raghawendra, 11).
The statement has been terminated.

UNIQUE INDEX AND NULL

NULL is some special, it is a marker that indicates the missing information or not applicable. NULL is even not equal to itself, however, when it comes to unique index, SQL Server treats NULL values the same. It means that if you create a unique index on a nullable column, you can only have once NULL value in this column. Let’s understand it with some examples – will create a new table Stud and define a unique index on the ID column –

Syntax:

CREATE TABLE Stud (
ID INT,
Name VARCHAR(100))

CREATE UNIQUE INDEX ix_Stud_ID
ON Stud(ID)

Now we will insert NULL value into this Stud table –

Syntax:

INSERT INTO Stud (ID, Name)
VALUES (NULL,’Raghawendra’)

Inserted this data into Stud table with NULL data into ID column, if we will insert some data into this table then SQL Server produces an error due to duplicate NULL values –

Syntax:

INSERT INTO Stud (ID, Name)
VALUES (NULL,’Raghawendra’)

Message thrown as mentioned below –

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.Stud’ with unique index ‘ix_Stud_ID’. The duplicate key value is ().
The statement has been terminated.

UNIQUE INDEX VS UNIQUE CONSTRAINT

Both unique index and unique constraints enforce the uniqueness of values in one or many columns.

SQL Server validates duplicates in the same manner for both unique index and unique constraint.

When we create a unique constraint behind the scene SQL Server creates a unique index associated with this constraint.

However creating a unique constraint on columns makes the objective of the unique index clear.

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply