An index helps SQL Server retrieve data from rows quickly. Indexes work like the table of contents at the beginning of books, allowing you to look up the page a topic is on fast. 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 non-clustered. Find out the difference between clustered and non-clustered indexes and why they are important.
Clustered Index in SQL Server
In a clustered index, data rows are physically stored in an ordered manner based on the key value. Since the index includes the table and it 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 may be slow because the query optimizer scans the index in order until it finds the target index.
Non-Clustered Index in SQL Server
A non-clustered index contains the key values whose entry is a pointer called a 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 in the table. For rows without a clustered index, the pointer points directly to the table row.
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 execute 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 at and the column name to use in the index.
If you add a primary key to a table that has a clustered index already, SQL Server will create a non-clustered index with it.
To create a clustered index that doesn’t 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 Non-Clustered Index in SQL Server
To create a non-clustered 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 non-clustered index in the table you specify and includes the column you indicate.
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 non-clustered indexes improve querying time. If most of your queries are SELECT operations on several columns in the table, clustered indexes are faster. However, for INSERT or UPDATE operations, non-clustered indexes are faster since the query optimizer can locate the column directly from the index.
As you can see, these indexes work best for different SQL queries. Most SQL databases will therefore benefit from having at least one clustered index and non-clustered indexes for columns that are regularly updated.
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 non-clustered indexes to resolve bookmark lookup deadlocks by creating a non-clustered index for columns the queries involved access.