What is a SQL Server clustered index ? The definition of a SQL Server index is a structure, i.e. a file, stored physically in a disk. It’s like the master data file and the log data file that composes a database. Basically, to find the associated lines or rows faster, the index stores one or more key columns. So what’s the definition of a MS SQL clustered index ?
A table with a clustered index orders the data physically. In this case, the keys columns define this sort order.
Definition of a clustered index in MS SQL
The clustered table stores the data once. So, a table have only one unique clustered index. Note that the SQL Server database system table structures and stores tables with an order only in this case. You can choose the order descending or ascending at the creation of a table.
For the same reason if a given table do not have any clustered index then the data is stored without a specific order. It is the case for the non-clustered indexes. This page explains the indexes in depth.
For both types of indexes (clustered or not) the index can be unique, like a primary key. So, the key columns identify all rows with a unique value.
An index orders the data of the table physically on the disk
Moreover, the functional usage of the table directly impacts the decisions to have a unique index or not.
For example, let us consider a sales table, with years, months, and days. If the business users use the month extensively in the queries, then having a clustered non unique index is particularly useful. For example, to do a delete and replace of the current month sales. Or to access directly in the disk one given month.
Please note that the database management system maintains the indexes. The SQL Server service running the database is design for this maintenance task. Nevertheless, use a maintenance plan to check index fragmentation and rebuilt them explicitly to ensure efficient queries. Especially for tables with frequent updates and inserts, like large transaction systems for retail or Data warehouse projects.
Frequently Asked Questions about Clustered indexes in SQL Server
An index improves the performance of SQL queries by splitting physically the data of the table on the disk hosting the database. It can be compared to a tree with the branches being the partitions and the leaves the data.
The main difference is that a clustered index stores the data of the table in an ordered manner, while a non-clustered index does not keep the data ordered.
The best column to index is a functional decision to take based on the usage of the table. The insert, delete and update frequency are also to take into consideration. The main goal is to index a column that segregates the best the data.
It is recommended to use a SQL Server clustered index when you need to use a single index. Because one unique clustered index is allowed per table. If you need multiple indexes on the same table, then the non clustered indexes are best.
Below is an article from the T-SQL series, on how to calculate the time difference in hours, minutes and seconds.
Be the first to comment