Sql Server- Difference between unique and non unique index

Welcome to Appsloveworld Technologies , In the session, we learn about what a unique index is and the difference between a unique index and a unique constraint.

Before continuing with the post, I strongly recommend reading read the previous post of this series.

• How does database indexing work with real time example?
• Clustered and Non clustered index in sql server with real example

what’s a unique index?

A unique index is used to enforce the uniqueness of key values in the index. Let’s understand this with an example.

I have the sample SQL script here, which creates table TblUsers.And if you look at the script, the ID column is marked as a primary key column.

And by default, a primary key constraint creates a unique clustered index on that column if another clustered index doesn’t already exist in the table. Let’s look at this in action.

CREATE TABLE [dbo].[TblUsers](
[Id] [int] Primary Key,
[Name] [nvarchar](100) NULL,
[Email] [nvarchar](100) NOT NULL,
[Gender] [int] NOT NULL,
[RewardsPoint] [decimal](18, 2) NOT NULL
)

Let’s execute the SQL script to create a table, Command completed successfully.

So the table should have been created since we have marked the Id column as the primary key column.

A unique clustered index should have been created on that column for in the table. And obviously, to find that out, we can make use of the system stored procedure,sp_helpindex, passing in the name of the table, which will list all the indexes that are available for this table.

Execute sp_helpindex TblUsers

So let’s execute this. As you can see on the below image we have a unique clustered index on the ID column.

So within the object explorer, refresh the tables folder and you should see the table expand that, expand the index folder and you should see an index that we have just created.

And look at the name it says it’s just a clustered index. It doesn’t tell us whether it’s a unique or non-unique index.

To identify that, you can click on the index, select properties and you should see a unique checkbox checked, which proves that this is a unique clustered index.

How did we get this unique clustered index?

Because we marked the Id column as the primary column. It created this unique clustered index behind the scenes.  Since we marked the Id column as a primary key, there should be a primary key as well with that column.

So when I expand the Keys Folder, look at the primary key in the Keys folder. Look at the primary key and look at the index.

They’re actually the same thing, PK__TblUsers and random number.

So in reality, the primary key constraint actually uses a unique index behind the scenes, to enforce, the primary key constraint.

Let’s prove that. Let’s try to drop this index.

And obviously to drop the index we can use the drop index statement drop index. Any time you drop an index you’ll need to specify the table name as well.

Let’s execute the below drop script and see what’s going to happen.

drop index TblUsers.PK__TblUsers__3214EC0799AA7AF9

An explicit drop index is not allowed on this index because it’s being used for primary key constraint enforcement.

So this message proves that the primary key uses a unique index behind the scenes to enforce the constraint.

if you’re not able to drop the index using drop index statement, you can actually do that using the object explorer.

So let’s try to delete this index from the object explorer. Right-click on the index and select delete.

Now refresh the folder, look at what’s happening , the primary key constraint is also gone.

So now if I try to insert duplicates into the table, it will accept the duplicates values.

INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'Test 2', 'pankajabcd@gmail.com', 0, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'Test 3', 'test@test.com', 1, CAST(31685.00 AS Decimal(18, 2)))

INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1, 'aakankshi6', 'aakankshi6@gmail.com', 3, CAST(109.00 AS Decimal(18, 2)))

select * from [dbo].[TblUsers]

this proves that behind the scenes, the primary key constraint actually uses a unique index to enforce that constraint.

So the uniqueness of an index is not a separate index by itself. It is just a property of an index. So both a clustered and a non clustered indexes can be unique.

If I want to create a unique non clustered index, for example, let’s say I want to ensure that in tblusers, no two users can have the same name and email.

Let’s say I want to enforce that, I can actually create a unique non clustered index in that case.

Create Unique NonClustered Index UIX_TblUsers_Name_Email
On TblUsers(Name, Email)

So I’m creating a unique non clustered index. And usually, for unique indexes, it’s better if you prefix that with UIX. So just by looking at the name, you can say that this is a unique index on TblUsers on the name and email columns.

Differences between a unique constraint and a unique index

If you want to enforce uniqueness across one or more columns, we will actually use unique key constraint.but now we are talking about using a unique index.

So what’s the difference between them? Actually, there are no major differences between a unique constraint and a unique index.

• when we add a unique constraint, a unique index gets created behind the pictures.

Let’s look at that in action. let’s say I have the table TblCustomers, we just got a Country column on that.

Now I want to ensure that, all countries are unique within the table. How do I do that? I can add a unique constraint. So let’s add a unique constraint. so obviously to add a unique constraint, we have to alter the table.

ALTER TABLE TblCustomers
ADD CONSTRAINT UQ_TblCustomers_Country
UNIQUE NONCLUSTERED (Country)

Let’s execute the command, and refresh the table folder expand the “keys” folder, and then expand the indexes folder.

Now look at this, we have index created and if you look at the type of the index, it’s a non clustered unique index.

If you want to create a clustered index, you can specify that as well

ALTER TABLE TblCustomers
ADD CONSTRAINT UQ_TblCustomers_Country
UNIQUE CLUSTERED (Country)

so whether you add a unique constraint or whether you create an index, there are two ways to create a unique index by adding a unique constraint or using create index statement and directly creating a unique index. 

So eventually to actually be creating a unique constraint over a unique index. Now we know that behind the scenes, a unique index gets created. Now you can do that through a constraint or directly using a create index statement.

when do we choose one option over the other?

Now to make our intentions clear, create a unique constraint when data integrity is the objective. this makes the objective of that index very clear.

In either case, this data is validated in the same manner and the query optimizer doesn’t really make any difference between a unique index that you have created by using a constraint or manually created that creates index statement.

Some useful points to remember

• we have already learned that by default, a primary key constraint creates a unique clustered index.
• we have also seen a unique constraint creates a unique non clustered index by default. these are only defaults and they can be changed if we wish to.

The post Sql Server- Difference between unique and non unique index appeared first on Software Development | Programming Tutorials.