Indexes in SQL

                                            Indexes in SQL

Index is a database object which help to fetch/retrieve data faster from SQL server. It is basically use to optimize query performance.

An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches. If you don't have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

                                      Query to create index:
                                                       Create Index IndexName
                                                       On TableName (ColumnName1,ColumnName2,....)

                                      Query  to rename index:
                                                       sp_rename 'IndexName','New_IndexName'

 Two main types of indexes are Clustered and Non-Clustered index

Clustered Index : A clustered index determines the order in which the rows of a table are stored on disk. If a table has a clustered index, then the rows of that table will be stored on disk in the same exact order as the clustered index.There can be only one clustered index created on a table.

Non-Clustered Index: A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes. A single table can have up-to 256 non-clustered index.

                                   

                                   Difference between Clustered and Non-Clustered Index

• A clustered index determines the order in which the rows of the table will be stored on disk – and it actually stores row level data in the leaf nodes of the index itself. A non-clustered index has no effect on which the order of the rows will be stored.
• A table can have multiple non-clustered indexes. But, a table can have only one clustered index.
• Non clustered indexes store both a value and a pointer to the actual row that holds that value. Clustered indexes don’t need to store a pointer to the actual row because of the fact that the rows in the table are stored on disk in the same exact order as the clustered index – and the non-clustered index actually stores the row-level data in it’s leaf nodes.