INDEX:
- An index is used to speed up searching in the database.
- 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.
- 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.
- Records are searched sequentially without an index it is called table scan. Table Scan scans the rows from the table.
- Records are scan by the B-Tree structure it is called an Index scan.
- STATISTICS IO provides detailed information about the impact that your query. Here logical reads are 5.
- 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.
- 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.
- The table scan uses 5 logical reads. When we use Index 4 logical reads are used.
- Indexes make a search faster but it decreases the performance of when insert, updates and delete.
If you are a newbie to database learning- SQL Server recommended is the following must-watch video: -
