Follow @endyourif rss Facebook LinkedIn

SQL Server Clustered Indexes vs. Nonclustered indexes SQL Server Clustered Indexes vs. Nonclustered indexes

Published on Jul 28, 2022

In this article, we'll discuss about the difference between clustered indexes and nonclustered indexes with SQL Server.

The clustered index is an internal data structure that stores all the records in a table. It is also called the primary key because each record has its unique identifier stored in the first column of the row. By the end of this article hopefully I will have answered the question of the difference of the clustered index vs nonclustered index with sql server.

Clustered index and nonclustered index using SQL Server

We will also see how to create clustered index (typically created on a primary key to create index) and nonclustered index using SQL Server Management Studio. Indexes can be created on a primary key especially when creating clustered indexes.

A clustered index is used by SQL Server when creating tables and is typically a unique index. This type of an indexes are automatically created when you add a new column to a table. You cannot modify the existing clustered index.

Nonclustered indexes are not automatically created in the table when you add columns to a table. They must be manually added to the table to create index with the following examples.

Just in case you're unsure about this, be sure to read more about the importance of database indexing when the indexes are created, typically to create a unique index to speed up queries of the table on columns that are an index key if you want to speed up the query especially when you create unique index.

Difference Between Clustered Indexes and a NonClustered Indexes

There are two main differences between clustered and a nonclustered indexes. First, clustered indexes store data in order. If you insert rows into a table without using a clustered index, the rows will appear out of order. Second, clustered indexes are unique. That means that only one row can exist in each cluster with an index on the table as indexes are useful on the query.

A clustered index is an index that stores data by row rather than column. This means that the order of rows within the index is not significant. If you insert new records into a table, they will automatically appear at the end of the clustered index.

Create Clustered Index

A clustered index stores data in an ordered manner on a table. This makes searching through the data faster because the database engine knows where to find the data. It also allows the database engine to use an index seek operation instead of a scan operation.

Let's look at an example to create an index on a table or view as a clustered index where I specify the name of the index key for the primary key of the table Customer in SQL.

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

The clustered index is being created in the table called Customer on the Id field. This will significantly help with the query optimizer when querying the database with sql server.

Create a NonClustered index

To create a nonclustered index, right click in the table name and select “Create Index”. You will see the following screen. Select the columns you wish to add as part of the index. Click OK when done.

The index is created on one or more columns on the table because there is no limit on the number of columns indexes can be created on with the index as a clustered index or nonclustered index.

In the following example the index is created as a nonclustered index with one or more columns.

CREATE NONCLUSTERED INDEX [IX_Customer_AccountId_CurrencyId] ON [dbo].[Customer]
(
	[AccountId] ASC,
	[CurrencyId] ASC
)
INCLUDE([Id],[CreatedTimestamp],[ActivationTimestamp],[CancellationTimestamp],[MonthlyRecurringRevenue]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

This is a nice example of creating an index on the table called Customer that includes the AccountId and CurrencyId so when I perform a query with the where clause of AccountId and CurrencyId will help with the query optimizer.

What is an index in sql server?

A SQL clustered index is use to improve the performance of fetching data from the table with sql server. The nonclustered index on the table or view is definitely the best ways to improve the performance of queries and applications. The following SQL index is can be a clustered index to create a faster search result of the table.

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | indexes

About Jamie Munro

Welcome to my blog. I have been compiling articles since 2009! Below is a list of every post I've ever created. Some of my major focuses have been compiling the best source of jQuery tutorials on the Internet today. A recent focus of mine is now focusing on creating the best SQL Tutorials for Beginners, Intermediate and Advanced Users. This is a compiliation of all my experience about how to do common SQL commands to optimize for performance. As you might see from the links around here, I've also written and published 5 books. My first and last book are focused around ASP.NET MVC and Web API tutorials. The last book has a big focus on integrating Knockout.js as the MVVM for the front-end code. O'Reilly Media was so interested in Knockout.js that they actually commissioned me to write that book first as it is one of the only books dedicated entirely to this MVVM library. So if you're looking for the best Knockout.js tutorials, I've compiled the best list. My first book, even though it was self published, was on the PHP framework: CakePHP. I used this framework for years and many of my first ever exampes were all focused on CakePHP tutorials that provide so many fantastic PHP examples. There are so many Javascript tutorials out there, but I noticed that one of the things I work on most with Javascript is arrays. With this I've worked hard on compiling a big list of really advanced Javascript array examples. These array examples are amazing including how to group by, get distinct array elements and so many more. I've also dabbled a little bit with Node.js and during those experiments I definitely ran into some common errors like Can't set headers after they are sent, Solving No Access-Control-Allow-Origin with Node js and Express, and Uncaught ReferenceError: require is not defined. With these common errors I've compiled a list of Node.js tutorials that help solve these problems, but more importantly getting you started with the basics.