Best scenarios to create CLUSTERED index on the SQL Server table

By Santhosh N

This explains the best scenarios to create CLUSTERED index on the table in SQL Server.

Following are the scenarios which work good by having a CLUSTERED index on the table:


1) When the table column contains wide range of distinct values.
2) When you are required to query the columns in a sequential manner (like firstname, middlename, lastname), then index also needs to be created in the same order.
3) When the query is having WHERE clause with BETWEEN or < and > conditions.
4) When the query have a GROUP BY and/or ORDER BY clause(s) which accesses some of the first few columns or all of the columns included in the CLUSTERED index.

Related FAQs

This explains about the creation of NON-CLUSTERED indexes and maximum allowed.
This explains how (in which order) the SQL Server stores the records in the table.
This explains how to set DEADLOCK_PRIORITY in SQL Server and its benefits in doing so.
This explains how to use TEMP tables in the SELECT query for better query performance.
This explains the advanced query tuning concepts of the SQL Server in terms of using joins
This explains the QUERY_GOVERNOR_COST_LIMIT option for controlling long running queries in the SQL Server.
Best scenarios to create CLUSTERED index on the SQL Server table  (1076 Views)