What Is the Difference Between Clustered and Non-Clustered Indexes in Microsoft SQL Server?

What Is the Difference Between Clustered and Non-Clustered Indexes in Microsoft SQL Server?

An index helps SQL Server retrieve row data quickly. Indexes work like the table of contents at the beginning of books, allowing you to quickly find the page a topic is on. Without indexes, SQL Server has to scan all the rows in a table to find a record.

There are two types of indexes in SQL Server: clustered and nonclustered. Learn the difference between clustered and nonclustered indexes and why they are important.

Clustered Index in SQL Server

In a clustered index, rows of data are physically stored in an ordered fashion based on key value. Since the index includes the table and you can only arrange rows in one order, you can only create one clustered index per table.

While indexes make retrieving rows in a range faster, INSERT and UPDATE statements can be slow because the query optimizer scans the index in order until it finds the target index.

Nonclustered index in SQL Server

A nonclustered index contains the key values ​​whose input is a pointer called the row locator. For clustered tables (tables with a clustered index), the pointer points to a key in the clustered index, which in turn points to the row of the table. For rows without a clustered index, the pointer points directly to the row in the table.

How to create a clustered index in SQL Server

When you create a table with a primary key, SQL Server automatically creates a clustered index key based on that primary key. If you don’t have a primary key, you can run the following statement to create a clustered index key.

CREATE CLUSTERED INDEX <index name>
ON TABLE <table_name>(column_name)

In this statement, you are specifying the name of the index, the name of the table to create it, and the name of the column to use in the index.

If you add a primary key to a table that already has a clustered index, SQL Server will create a nonclustered index with it.

To create a clustered index that does not include the primary key column, you must first drop the primary key constraint.

USE database_name
ALTER TABLE table_name
DROP CONSTRAINT pk_name
GO

Removing the primary key constraints also removes the clustered index, allowing you to create a custom one.

How to create a nonclustered index in SQL Server

To create a nonclustered index, use the following statement.

CREATE INDEX <index name>
ON TABLE <table_name>(column_name)

You can also use the NONCLUSTERED keyword like this:

CREATE [NONCLUSTERED] INDEX <index name>
ON TABLE <table_name>(column_name)

This statement creates a nonclustered index on the table you specify and includes the column you specify.

If you want, you can sort the columns in ascending (ASC) or descending (DESC) order.

CREATE [NONCLUSTERED] INDEX <index name>
ON TABLE <table_name>(column_name ASC/DESC)

Which index should you choose?

Both clustered and nonclustered indexes improve query time. If most of your queries are SELECT operations on multiple columns of the table, clustered indexes are faster. However, for INSERT or UPDATE operations, nonclustered indexes are faster because the query optimizer can locate the column directly from the index.

As you can see, these indexes work best for different SQL queries. Therefore, most SQL databases will benefit from having at least one clustered index and non-clustered indexes for columns that are updated regularly.

The importance of indexes in SQL Server

Clustered and non-clustered indexes lead to higher query performance. When you run a query, the query optimizer scans the index for the storage location of a row and then retrieves information from that location. This is much faster than scanning all the rows in the table.

You can also use nonclustered indexes to resolve bookmark search deadlocks by creating a nonclustered index for the columns accessed by queries.

Leave a Reply

Your email address will not be published. Required fields are marked *