Articles → SQL SERVER → Unique Index In SQL Server

Unique Index In SQL Server

In this article, we will discuss unique index in SQL server.

Purpose Of Unique Index

If unique index is created on a column, then it means

Only unique values are allowed
NULL value could be inserted.

Syntax

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Example

First, create a table. See the screen shot for table schema.

Picture showing the schema of the sample table

Click to Enlarge

Now, add unique index on the column "Test" using following syntax

CREATE UNIQUE INDEX idx_unique_SampleTable_Test ON SampleTable (Test)

Next thing is, how to test if unique index is created successfully or not. For that we will execute following insert queries.

insert into SampleTable 
values 
  (null) insert into SampleTable 
values 
  ('value1') insert into SampleTable 
values 
  ('value2') insert into SampleTable 
values 
  ('value1')

When you execute this query, you will get following output.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 2601, Level 14, State 1, Line 4

Cannot insert duplicate key row in object 'dbo.SampleTable' with unique index 'idx_unique_SampleTable_Test'. The duplicate key value is (value1).

The statement has been terminated.

The reason for this error is, we are trying to insert a duplicate value in the column. But as unique index is created on that column. That is why error is thrown.

Posted By -	Karan Gupta

Posted On -	Sunday, January 1, 2017

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250