SQL Server Unique Index

In previous post, we have learnt about Index and its types. In this post, we will learn about unique index. In SQL Server, a unique index ensures that the values in the indexed columns are unique across all rows in the table. This means that no two rows can have the same combination of values in the indexed columns.

Syntax:

CREATE UNIQUE INDEX [IndexName]
ON TableName (Column1, Column2, ...);

Let's break down this syntax:

• CREATE UNIQUE INDEX: This statement is used to create a unique index.
• IndexName: Replace this with a meaningful name for your index. It's optional, and if you omit it, SQL Server will generate a name.
• ON TableName: Specify the name of the table on which you want to create the unique index.
• (Column1, Column2, ...): List the columns on which the index will be created. The combination of values in these columns must be unique for each row in the table.

Example. Suppose you have a table called Employee with columns ID and Email, and you want to ensure that each email address is unique:

CREATE UNIQUE INDEX IX_Employee_Email

ON Employee (Email);

This will create a unique index named IX_Employee_Email on the Employee table, using the Email column. The unique index ensures that no two rows in the Employee table can have the same email address.

If you attempt to insert or update a row with a combination of values in the indexed columns that already exists in another row, SQL Server will raise a violation error, preventing the operation.

It's worth noting that a unique index can be created on a single column or multiple columns, depending on the unique constraint you want to enforce. Additionally, a unique index can be applied to a column that allows NULL values, with the exception that multiple rows can have NULL values in the indexed columns. If you want to enforce uniqueness even for NULL values, you can use a filtered index or a unique constraint with a combination of NULL and non-NULL values

Example of Unique Index

Unique index is automatically created when a column has unique constraint.

Create the following table.

CREATE TABLE EmployeesMobiles(
     id int,
     first_name varchar(10),
     last_name varchar(10),
     MobileNo int UNIQUE
)

We find that Index is created automatically on MobileNo column.

Unique index is automatically created when a table has unique constraint. We can use unique constraint upon more than one column. For example, first name and last name combination can be unique. For this we use table level unique constraint.

Create the following table.

CREATE TABLE EmployeesNameUniq(
     id int,
     first_name varchar(10),
     last_name varchar(10),
     Salary int,
     UNIQUE (first_name, last_name)
)

The unique index is created as shown below.

Insert some rows into the table.

INSERT EmployeesNameUniq Values(2, 'Ram', 'Kumar', 2500)
INSERT EmployeesNameUniq Values(1, 'Gaurav', 'Singh',4500)
INSERT EmployeesNameUniq Values(5, 'Rima', 'Jain', 5400)

 

Now try to insert same name row.

INSERT EmployeesNameUniq Values(3, 'Rima', 'Jain', 8800)

It will throw error.

What is difference in the indexes in the following cases?

CREATE TABLE tblA(
     id int primary key
)

CREATE TABLE tblB(
     id int unique
)

CREATE TABLE tblC(
     id int unique not null
)

In the next post we will learn about Filtered Index in SQL Server.