Clustered Index and Non-Clustered Index in SQL Server

Here we are going to discuss clustered and non-clustered indexes in SQL Server; to understand these indexes clearly we should first know what database indexes are. So let's have a look at database indexes.

DATABASE INDEXES

Database indexes are copies of your data in a table that is sorted a certain way.

Database administrators can gain potential performance benefits through the judicious use of indexes on database tables. Indexes allow you to speed query performance on commonly used columns and improve the overall processing speed of your database. Also we can say that an index is used to fetch the data in a reliable and fast manner. The different types of indexes are:

SQL Server supports two main types of indexes: Clustered and Non-Clustered. They are implemented using a B-tree structure and at the top of each index is the root node, which contains index rows. These store pointers to the next level of index tree. Each index can have multiple intermediate nodes, which in turn contain pointers to the next level of the index until we reach the lowest level, said to be the leaf level.

CLUSTERED INDEX

In SQL Server the clustered indexes are a critical consideration in the overall architecture of the database. They are often overlooked, misunderstood or, if the database is small, considered unimportant. They determine the logical order in which table data is stored because the leaf/lower level of the clustered index consists of the actual data pages of the table. A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key.

Only 1 clustered index is allowed per table so choose wisely and we should consider choosing the columns on which this index will be created carefully. There are several considerations for choosing a clustering index key which should be narrow, as it serves as the row's identifier and is present in all non-clustered indexes. It should also be unique because if a clustered index is not unique SQL will make it unique by adding a hidden 'unifier' column to rows with duplicate values. A clustered index's key columns should not change because if the key values change for a row, that row must be physically moved. Fragmentation is minimized when a clustered index key's inserted values are always larger than existing values.

Structure of Clustered Index

NON-CLUSTERED INDEXES

Non-Clustered indexes are stored separate from the table. They are created outside of the database table and contain a sorted list of references to the table itself. In SQL Server 2005 and earlier, a maximum of 249 non-clustered indexes could be created on a table but now in SQL Server 2008 that limit has been increased and now 999 non-clustered indexes can be created on a single table. Non-clustered indexes are sorting of the columns not copies of the table, you specify that "point" back to the data pages in the clustered index. This is why the clustered index you choose is so important because if effects all other indexes.

There are 2 modes for non-clustered indexes: Non-Unique and Unique.

One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.

Structure of Non-Clustered Index

Other indexes with a brief description are:

Unique Indexes

A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Both clustered and non-clustered indexes can be unique.

Index with included columns

A non-clustered index that is extended to include nonkey columns in addition to the key columns.

Spatial Index

A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

Filtered Index

An optimized non-clustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

XML

A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the XML data type column.

Full-text

A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.

For more information please read here>>