SQL Server Indexes

What is index?

In English Indexes are defined as something that serves to guide, point out, or otherwise facilitate reference. And the same concept applies to the SQL Server. To understand this concept let’s take example of Telephone Directory. Assume that data is written in the telephone directory on the basis of person applying for connection there is no sorting based on name or anything. Just imagine how much effort you have to do in order to search one name from that directory. So if you have to search name ‘Roli’ you have to go through all the records till you find Roli. Now in next scenario if the data is arranged alphabetically how will you search? You will open pages with R then Ro then Rol and then you can go through records to find name Roli.

Why to use indexes?
Index helps in faster data retrieval of the data and reduces number of I\O operations of the server.

Types of indexes present in SQL Server?

There are two types of Indexes in SQL Server.
1. Clustered
2. Non Clustered

Architecture of Clustered Index

Properties

• Clustered Index is that its leaf pages in contain data pages.
• There is only one Clustered index possible on any table in SQL Server. The database engine allows the creation of a single clustered index per table for the reason that data is physically sorted in case of Clustered indexes. And only one time you can sort any data physically .Let’s go back to our example of telephone directory If the data is sorted based on first name you cannot sort it again by Surname.
• A clustered index is built by default for each table for which you define the primary key using the primary key constraint. Also each clustered index is unique by default that is each data value can appear only once in a column for which the clustered index is defined. If a clustered index is built on a non-unique column the database system will force uniqueness by adding 4 byte identifier to the rows that have duplicate values.
• Clustered Index allows very fast access in cases where a query searches for a range of values.

Syntax

Architecture of Non-Clustered Index

 

 

 

 

 
Properties

• A non clustered index does not change the physical order of the order of the rows in the table.
• The leaf pages of the non clustered index consist of an index key plus a bookmark.
• For each  non Clustered  Index SQL Server engine creates an additional index structure that is sorted in index pages. 

Significant differences between Clustered & Non Clustered Index 

• A non clustered index does not change the physical order of the rows in the table.
• The leaf pages of a non clustered index consist of an index key plus a bookmark.