SQL Server Clustered Indexes vs. Nonclustered indexes

• Clustered index and nonclustered index using SQL Server
• Difference Between Clustered Indexes and a NonClustered Indexes
• Create Clustered Index
• Create a NonClustered index
• What is an index in 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.

Published on Jul 28, 2022

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