Index, Clustered index and Non-clustered index in SQL-part 6

  In this blog we are going to understand  Index, Clustered index and Non-clustered index in SQL,

Index:

• An index improves the performance of search.
• An index makes a search faster by using a Balanced Tree (B-Tree) Structure. In B-Tree Structure data divided into root node, Non- leaf nodes and Leaf nodes.
• Index can make your insert, update and delete can become slove.

two types of index:

1.Clustered Index

2.Non-clustered Index

1.Clustered Index:

• A Clustered Index stores the actual data at the leaf node.
• The Clustered index will be automatically created when the primary key is defined.

2.Non-clustered Index:

• The Non-Clustered Index does not contain actual data. It uses a clustered index for fetching the data.
• Non-Clustered Index used to increase the speed of queries.

Example: If data from 1 to 100 and we want to search 80.The B-Tree divided data into two sections that are 1 to 50 and 51 to 100. Data 80 is greater than 50 therefore, the first section 1 to 50 is skipped only 51 to 100 is searched and gets the data.

how to create clustered index:

By creating index goto the design of the table. Then right-click on column and click on indexes/keys.

After that new pop-up window is open. In the Table designer, select the clustered index.

                                                                

How to create non-clustered index:

    

      

• When we execute the query and then click on Display Estimated Execution Plan it shows the execution plan.
• It uses the Clustered Index Seek. Clustered Index Seek scanning a particular range of rows from a clustered index.

     

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!