What is a SQL Server clustered index?

A SQL Server clustered index definition? A SQL Server index is a structure, i.e. a file, stored physically in a disk. Unlike the master data and the log data files that are the main files composing a database. One or more key columns are stored in the index, basically to find the associated lines or rows faster.

A table with a clustered index must have its data ordered, and the columns used as keys defines this sort order.

Which definition for a SQL Server clustered index?

The clustered table stores the data once. So, a table have only one unique clustered index. Note that a SQL Server table is structured and stored 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’s 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, all rows identified by a unique value composed of the key columns.

A clustered index orders the data of the “clustered table” physically in the disk.

Moreover, the functional usage of the table impacts directly the decisions to have a unique index or not.

For example, let’s 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 very 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 transactional systems for retail or Datawarehouse projects.

Be the first to comment

Leave a Reply

Your email address will not be published.