Unique Index in the SQL Server

 A unique index is a type of database index that ensures all the values in the indexed column or columns are distinct (i.e., no duplicate values are allowed). It enforces uniqueness on the data, similar to a unique constraint, while also optimizing the speed of data retrieval for the indexed columns. The important fact is that there are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint.

The important fact is that there are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint.

Benefits of a Unique Index

ü  They give guarantee for multi-column unique indexes that each combination of values in the index key is unique.

ü  They give guarantee that the data in each column is unique. It is possible to a table have a unique clustered index and multiple unique non clustered indexes.

ü  They ensured the data integrity of the defined columns.

ü  They provide additional information helpful to the query optimizer that can produce more efficient execution plans.

Types of Unique Indexes

Based on the key constraints on the table, Unique Indexes can be Unique Clustered Index or Unique Non clustered Index such as –

ü  When you are creating a Primary key constraint on the table then Unique Clustered Index created automatically.

ü  When you create a UNIQUE constraint, a Unique Non clustered Index is created to enforce a UNIQUE constraint by default.

Syntax of unique index

CREATE UNIQUE INDEX Index_name ON Table (Column name) ; 

CREATE TABLE emp(                 ID int IDENTITY(1,1) NOT NULL,                 E_name varchar(50) NULL,                 E_mobile varchar(10) NULL,                 E_add  varchar(50) NULL,                 PAN_Number     varchar(10)                 ) CREATE UNIQUE INDEX UX_emp_PAN_Number ON emp (PAN_Number) ;

 Refer: Insert Null Value in Unique column(Enforcing Uniqueness with NULL)

https://bageshkumarbagi-msbi.blogspot.com/2019/10/insert-null-value-in-unique-column.html

We can create a multiple unique index on a table.