Introduction:

When it comes to optimizing query performance in SQL Server, indexes play a crucial role. In this article, we will focus on one specific type of index called the clustered index.

What is a Clustered Index?

A clustered index is a type of index in SQL Server that organizes data in a B-tree structure. This structure allows for quick searching and retrieval of data, eliminating the need for a table scan.

How does a Clustered Index work?

Imagine a library with thousands of books. If you were to search for a specific book without any organization, you would need to scan through each book until you find the one you’re looking for. This is similar to a table scan in SQL Server.

However, with a clustered index, the data is organized in a B-tree structure. This structure consists of three levels:

• Root Node: The top node that contains pointers to intermediate index pages or leaf data pages.
• Intermediate Level: Contains index key values and pointers to the next intermediate level or leaf data pages.
• Leaf Node: Contains the actual data pages.

When you perform a search using a clustered index, SQL Server reads the root page, then the intermediate node, and finally the leaf node to retrieve the required data. This process is known as an index seek.

Creating a Clustered Index:

To create a clustered index in SQL Server, you can use either the SQL Server Management Studio (SSMS) GUI or T-SQL method.

Using SSMS GUI:

1. Create a test table:

CREATE TABLE dbo.bookstore (
    book_id INT NOT NULL,
    book_name VARCHAR(100)
);

2. Insert data into the table:

INSERT INTO dbo.bookstore VALUES (1, 'Learn ABC of SQL Server');
INSERT INTO dbo.bookstore VALUES (2, 'Advanced troubleshooting step SQL Server');

3. Right-click on the table, go to Indexes, and select New Index.
4. In the General page, specify the index name and select the index type as Clustered Index.
5. Add the index key column(s) and specify the sort order.
6. Configure other options such as auto recompute statistics, ignore duplicate values, and allow online DML processing.
7. In the Storage page, specify the filegroup for the index.
8. Click OK to create the clustered index.

Using T-SQL:

CREATE CLUSTERED INDEX IX_bookstore_clustered
ON dbo.bookstore (book_id ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON [PRIMARY];

Checking Clustered Index Levels:

You can use the DMV sys.dm_db_index_physical_stats to check the index fragmentation level and index levels for the clustered index.

SELECT avg_page_space_used_in_percent, avg_fragmentation_in_percent, index_level, record_count, page_count, fragment_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), OBJECT_ID('YourTableName'), NULL, NULL, 'DETAILED');

Conclusion:

In this article, we have explored the concept of a clustered index in SQL Server. We have learned how a clustered index works, how to create it using SSMS GUI or T-SQL, and how to check its levels using the sys.dm_db_index_physical_stats DMV. By understanding and utilizing clustered indexes effectively, you can significantly improve query performance in your SQL Server databases.