MS SQL Server - Index Internals III

Today’s discussion continues on the different types of Indexes available on MS SQL Server.

Unlike from my previous example of index on the back of an Atlas, which normally has one index, a table may have multiple indexes. These indexes can be defined on one or more columns of the tables. The two major types of indexes in SQL Server are Clustered and Non-clustered Indexes.

Clustered Indexes: A clustered when defined on a column of a table, the data of the table in the data pages are sorted in order of the column on which index is defined. This column is referred as an Index Key. Since the data is sorted physically on the disk, the leaf page of the index pages are the data pages of the table.

Inserting a new row in a table with clustered index defined, SQL Server ensures that the row is placed in the correct physical location in key sequence order. Structure of the Clustered Index is as given below:Non-Clustered Indexes: A Non-clustered index contain only the index key (column on which non-clustered index is defined) and a reference to find the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order (which can be dictated by a clustered index). If no clustered index is created on the table, the rows are not guaranteed to be in any particular order.

Non-clustered index structure is depicted as: At any given level in the index, the pages are linked together as shown in Figure below, and this is true regardless of whether the index is a Clustered index or a Non-clustered index.
Both the clustered and non-clustered indexes can be defined on one or more columns of a table, to serve the frequent database queries. However there can be only one clustered index defined on a table while there can be 249 non-clustered indexes created on a table.