Difference Between Clustered And Non-Clustered Index In SQL Server

What Is Indexing?

Indexing is a way of optimizing the performance of a database by minimizing the number of disk accesses required when a query is processed. It is a data structure technique which is used to quickly locate and access the data in a database.  There are two types of indexes in SQL Server:

• Clustered Index
• Non-clustered index

What Is Clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can only be one clustered index per table, because the data rows themselves can be stored in only one order. The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is referred to as a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

What You Need To Know About Clustered Index

1. A clustered index is a kind of file that sorts the information in the table on their key qualities.
2. It can be used to sort the record and store the index in physical memory.
3. The size of the clustered index is quite large.
4. It stores records in the leaf node of the index.
5. In clustered index, clustered key defines order of data within table.
6. Clustered indexes are faster to read than non-clustered indexes as data is physically stored in index order.
7. Clustered index store pointers to block not data.
8. A table can have only one clustered index.
9. No extra space is required to store logical structure.
10. A clustered index can improve the performance of data retrieval.
11. It is slower for insert and update operations than non-clustered index.
12. If the table does not have clustered index, it is referred to as a ‘’Heap’’.
13. A clustered index always has index Id of 0.

What Is Non-Clustered Index?

A non-clustered is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index. In a non-clustered index, the leaf pages of the index do not contain any actual data, but instead contain pointers to the actual data. These pointers would point to the clustered index data page where the actual data exists or the heap page if no clustered index exists on the table.

A non-clustered index in SQL Server stores the index structure separately from the data physically stored in a table. SQL server allows you to create almost 999 non-clustered index per table. The pointer from an index row in a non-clustered index to a data row is referred to a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.  For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. You can add nonkey columns to the leaf level of the non-clustered index to by-pass existing index key limits and execute fully covered, indexed, queries.

What You Need To Know About Non-Clustered Index

1. A non-clustered index stores the meta-information in one area and files in another area. The file comprise of the pointers to the location of that information.
2. It creates a logical ordering of data rows and uses pointers for accessing the physical data files.
3. The size of the non-clustered index is small compared to the clustered index.
4. It does not store records in the leaf node of an index that means it takes extra space for data.
5. It can work with unique constraints that act as a composite key.
6. Non-clustered indexes are slower in read operation as compared to clustered indexes.
7. Non-clustered index store both value and a pointer to actual row that holds data.
8. A table can have multiple non-clustered indexes.
9. Extra space is required to store logical structure.
10. Non-clustered index creates the columns that are used in joins.
11. It is quicker for insert and update operations than clustered index.
12. A table may not have any non-clustered indexes.
13. A non-clustered index always contains an index Id>0.

Also Read: Difference Between Strong And Weak Entity

Difference Between Clustered And Non-Clustered Index In Tabular Form