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) ; |
https://bageshkumarbagi-msbi.blogspot.com/2019/10/insert-null-value-in-unique-column.html
We can create a multiple unique index on a
table.
