Clustered and Non clustered index in sql server with real example

Welcome to Appsloveworld technologies,In this session, we learn about the different types of indexes that are available in the SQL server, what are clustered and non clustered indexes, and then difference between them.

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

• How does database indexing work with real time example

The following are the different types of indexes that are available in SQL server

1. Clustered
2. Nonclustered
3. Unique
4. Filtered
5. XML
6. Full text
7. Spatial
8. Columnstore
9. Index with included columns
10. Index on computed columns.

In this post will be talking about clustered in non clustered indexes and the difference between them.

So what’s a clustered index?

A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index. Source

We have a create table statement here, where we are creating a TblUsers table. And if you look at this closely, the Id column is marked as a primary key column.

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

)

A primary key constraint will automatically create a clustered index on that column.

Here ID is marked as a primary key column. So when I create this table using the “create table” statement, it’s going to create a clustered index automatically on the Id column for this table.

Let’s look at this in action. Let’s execute the above query.

Now, let’s check in the database if it has created a clustered index on this ID automatically

how do we check that? There are two ways.
One way is to basically use sp_helpindex for getting information about indexes. so it shows that we have a clustered index on this ID column.

 

we did not create this explicitly. It got automatically created because a primary key constraint will automatically create a clustered index on that column if the table doesn’t have any clustered index. if look at this, it says it’s a unique clustered index.

Learn more about unique indexs

• what is a unique index

Unique index is basically used by SQL Server to enforce the uniqueness of the primary key. we have talk about unique indexes in detail in the what is a unique index post.

What is the other way of checking the indexes on this table?

The other one is basically to use, the object explorer within the object explorer, expand the TblUsers, and expand the index folder. You should see the index that has just been created.
So now we just have created the table TblUsers. Now let’s insert some data into that table. I have some insert statements here. And if you look at the below script, we are inserting values for id, name, email, gender, and RewardsPoint.

INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', 'pankajabcd@gmail.com', 0, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', 'test@test.com', 1, CAST(31685.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', 'aakankshi6@gmail.com', 3, CAST(109.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', 'test3@test.com', 0, CAST(109.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', 'test02@gmail.com', 0, CAST(5955.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', 'alokpatel@gmail.com', 1, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2012, 'Aakankshi Gupta', 'aakankshi@gmail.com', 0, CAST(127.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', 'test1@test.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', 'pankajws@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', 'contact@adequateinfosoft.com', 1, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', 'neirajsingh101@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2, 'rajat Saxena', 'rajat.nirmal@gmail.com', 1, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (3, 'Pankaj Patel', 'Pankaj@gmail.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', 'pankajabc@gmail.com', 2, CAST(30.00 AS Decimal(18, 2)))
GO

Here, if you will look at the data closely. The values for the ID column are not in sequential order.

I am intentionally, inserting them in a nonsequential order.

we are told that a clustered index determines the physical order of data in a table. And then in table on the ID column, we have a clustered index.

So even if you insert data in nonsequential order, when you say select * from [dbo].[TblUsers], the data should have been automatically arranged in order.

• A table can have only one clustered index but one clustered index, can contain multiple columns and if a clustered index contains multiple columns within that we call that kind of an index as a composite index. Since it’s a cluster index, we can call it a composite cluster index.

Similarly, and non clustered index also can have multiple columns within that. In which case we call it a composite non clustered index

You can consider a telephone directory just like a composite index because, the numbers are organized by the last name first and then if there are similar last names for people, then the data is arranged according to their first names.

so in a similar fashion, we can create a composite cluster index on the TblUsers for name and Rewardspoints columns.

if you look at the way we have the data, it’s arranged on the in the order of the Id column because currently, we have a clustered index on the ID column.

but instead of that, I want to create a clustered index on the name and RewardsPoint columns together, which means I want to sort the data first by name and then by RewardsPoint.

Composite cluster index

So now let’s try to create a clustered index on the name and RewardsPoint. But remember, there is already a clustered index on the ID column.

Create Clustered Index IX_TblUsers_Name_RewardsPoint
ON TblUsers(Name ASC,RewardsPoint DESC)

So obviously when we try to create this clustered index on this table, we should get an error stating that you cannot create more than one clustered index on the table TblUsers, drop the existing cluster index before creating another one.

• So obviously we’ll have to drop that index.
• Go to the object explorer, expand the indexes folder.
• Right-click on the index and select delete and click.

that should delete the index.

so we have deleted the cluster index on the ID column. Now let’s try to create the clustered index on the name and RewardsPoint columns.

Basically this is a composite clustered index because your indexed is containing more than one column.

keep in mind you can only have one clustered index on a table. It’s not possible to have more than one clustered index, but it is possible for that one cluster index to have more than one column within that index keys.

Create Clustered Index IX_TblUsers_Name_RewardsPoint
ON TblUsers(Name ASC,RewardsPoint DESC)

Now let’s select the data from the TblUsers table. Before we created this index, it was arranged in the ascending order of the Id column. But since now we have created a clustered index on the name and RewardsPoint columns.
Now the data is first arranged, in the table in the order of name and then RewardsPoint within that name in descending order.

Non clustered index

if I have to create a non clustered index. So how do we create that? We say create nonclustered index. The only difference is instead of saying cluster, you specify nonclustered.

So here, if you look at the example, I am creating a nonclustered index on the name column for the TblUsers table.

Create NonClustered Index IX_TblUsers_Name_RewardsPoint
ON TblUsers(Name ASC)

Now when we spoke about clustered indexes, we understood that the data in the table is arranged based on the clustered index column.

Now when you create a nonclustered index this is analogous to an index in a textbook.

if you look at the index in a textbook, the index is stored in a separate place and the data is stored in a separate place. For example, at the beginning of the book, you have the chapter index.

Now, if I ask you to go to a specific chapter, you will first check the index, chapter 5 is on page number 400, so you will go to page 400.So the index is stored separately and the data itself is stored separately.

So the index in a book is a little different to an index in a telephone directory or a dictionary.In a telephone directory or a dictionary, the data is arranged in the alphabetical order.

Basically, the data itself is arranged. You don’t have separate index pages and separate data pages, but in a book, you have separate index pages and separate data pages. So similarly non clustered index is analogous to a book index.

The index itself is stored separately , for example, since we have created a Nonclustered, index on the name column, the names are arranged in descending order and then each name has a row address.

For example, if I write a query select * from [dbo].[TblUsers] where name=’Neeraj Singh’,

what is going to happen?

it goes to the table based on the row address and then directly fetch that record.

So since the non clustered index is stored separately from the actual table, a table can actually have more than one non clustered index.

Just like our book, and that can have an index by chapters at the beginning and maybe another index by common terms at the end of the book.

so the indexes that we see in a book, we can have as many indexes as we want of that kind. the same is applicable for non clustered indexes.

I can create one index on the name column, another index on the gender column. So there is no restriction on how many non clustered indexes you can have.

Difference between clustered and non clustered index with example

• The clustered index determines the storage order of rows in the table and hence doesn’t require additional disk space, obviously.But whereas non clustered indexes are stored separately, let’s say, for example, I have a million records here. You will have a million records in the index as well
• Since non clustered indexes are stored separately from the table itself, you require extra disk storage space. But for clustered index, it’s not the case because cluster in the index just determines the order of data in the table itself.You don’t require additional disk storage space.
• Cluster index is obviously faster than the non clustered index because the clustered index has to refer back to the table if the selected column is not present in the index.

what do we mean by that? For example, we are using a non clustered index. Now when I ask select * from [dbo].[TblUsers] where name=’Neeraj Singh’.

it first checks the index of “Neeraj Singh” records. This is the row address. And if I want the Id and Name, Email columns, I don’t have those columns in this index. So I will have to check and I will have to refer the table back.

Obviously there is one extra lookup involved if it’s a non clustered index.

But on the other hand, if it’s a clustered index, then all the columns are present in the table itself and the data itself in the table are arranged based on the clustered index.

So you don’t have that extra lookup. That’s why clustered indexes are slightly faster than non clustered indexes.

The post Clustered and Non clustered index in sql server with real example appeared first on Software Development | Programming Tutorials.