SQL - Unique Index

Indexes are most powerful feature of SQL because they provide opportunities for much more efficient use of I/O and they have great potential.
A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. It is not possible to create a unique index on a single column if that column contains NULL in more than one row as well as to create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.

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

1. They give guarantee for multi-column unique indexes that each combination of values in the index key is unique.
2. 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 nonclustered indexes.
3. They ensured the data integrity of the defined columns.
4. 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 Nonclustered Index such as –

1. When you are creating a Primary key constraint on the table then Unique Clustered Index created automatically.
2. When you create a UNIQUE constraint, a Unique Nonclustered Index is created to enforce a UNIQUE constraint by default.

Syntax for Unique Index

USE tempdb; GO -- Create a new table with three columns. CREATE TABLE Employee     (     EmpId int NOT NULL Primary Key,      Name varchar(30) NULL,      Address varchar(50) NULL      ); GO -- Create a Unique nonclustered index called Ind_Employee_EmpId -- on the dbo.Employee table using the name column. CREATE UNIQUE INDEX Ind_Employee_EmpName     ON dbo.Employee (Name); GO ---- How to see the indexes in the table EXEC sp_helpindex Employee ---Insert value into the table Insert into Employee Values (1, 'Ryan Arjun', 'India') ---- alter non-clustered index ALTER INDEX Ind_Employee_EmpName ON Employee REORGANIZE; ---- Check table size into the database EXEC  sp_spaceused Employee ---- drop employee table drop table Employee

Limitations and Restrictions

1. A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.
2. A unique non-clustered index can contain included non-key columns.

Type of Indexes in SQL

The following types of indexes are available in SQL Server-

1)  Clustered Index

2)  Nonclustered Index

3)  Unique Index

4)  ColumnStore Index

5)  Index with Included Columns Index

6)  Index on Computed Columns Index

7)  Filtered Index

8)  Spatial Index

9)  XML Index

10) Full-text Index
11) Indexed View
12) Partition Indexes