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

1. Only unique values are allowed

2. NULL value could be inserted.

Syntax

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Example

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

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