Index - Clustered & Non Clustered Index

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.


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 keysequence 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.


A table can have one of the following index configurations:
• No indexes
•A clustered index
•A clustered index and many nonclustered indexes
•A nonclustered index
•Many nonclustered indexes