SQL Server Indexes Tutorial | Different Types of Indexes in SQL Server | Difference between Clustered Indexes and Non-Clustered Indexes in SQL Server

Introduction:

Here I will explain about SQL Indexes and different types of indexes and advantages of indexes in SQL Server.

Description:

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Let’s see an example to illustrate this point suppose now we are reading book in that I need to check the information for dbmanagement to get this information I need to search each page of the book because I don’t know in which page that word information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information with dbmanagement word. By using second method we can save lot of time and we can get information in efficient way.

This same principle applies for retrieving data from a database table. Without an SQL Index, the database system reads through the entire table to locate the desired information. If we set the proper index in place, the database system first go through the index to find out where to retrieve the data, and then go to that location directly to get the needed data. This is much faster due to the SQL Index. Creating and removing indexes on table will not show any effect on application because indexes operate behind the scenes.

Syntax to Create SQL Index in table:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)

Example to create Index on table

CREATE INDEX SampleIndex ON UserInformation (UserName)

The above statement is used to create an index named “SampleIndex” on the “UserName” column in the “UserInformation” table

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

Example of creating SQL Index on multiple columns

CREATE INDEX SampleIndex ON UserInformation (UserName,FirstName)

To Drop Index on table use the below statement

DROP INDEX TABLE_NAME.INDEX_NAME

In SQL we are having two types of indexes are there

1)    Clustered Index

2)    Non-Clustered Index

 

Clustered Index

Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered Index

Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values For use on columns with high selectivity

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.