SQL Index and SQL Plan

-


Index :

  • Indexes are used to improve the performance of searching.
  • 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.
  • An index is a pointer to data in a table.
  • Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
  • Example :
  • Index has two types which helps to search fast :

1. Clustered Index :

  • A Clustered Index stores the actual data at the leaf node.
  • In SQL server clustered index is created automatically, when a primary key is created.
  • Only one clustered index is created per table.

Creating Clustered Index in SQL server : 

1.  Go to Design of a table, then right click on column and select Indexes/ Keys :

2.  After that in Table Designer set 'Create As Clustered' as Yes, See Clustered index is created :


2. Non-Clustered Index:

  • The leaf nodes of non-clustered index doesn't know where the data is? Leaf node of non-clustered index points to the clustered index's leaf node for searching the data.
  • We can create multiple non-clustered index per table.
  • 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.


Creating Non-Clustered Index in SQL server:

1.  Go to Design of a table, then right click on column and select Indexes/ Keys :

2.  Then click Add and select the column name , also select type to index :


SQL Plan :

  • The SQL plan is nothing but the plan by SQL server created while executing.
  • It plans how to execute the query.
  • We can directly click on “Display Estimated Execution Plan” toolbar button to check plan.
  • Simple table scan :
Index scan :
  • An index scan is used when SQL Server need to scan the data or index pages to find the appropriate records. 
  • Index Scan retrieves all the rows from the table. 
  • Example : 


Index seek :
  • Index seeks are generally used for the highly selective queries.
  • Index Seek retrieves selective rows from the table.
  • Example :



Thank You!!!

For more understanding watch below video :

Comments

Post a Comment

Popular posts from this blog

Day 3 : JavaScript

-
Image
 Data Types of JavaScript:  In JavaScript, their are three data types present i.e number, string and boolean. As we all know JavaScript is a dynamic language, means one variable can hold different datatypes.                    Example :                                      var x;                                      x=10;              //number                                      x="Hello"      //string                     // In above eg. the x holds number as well as string but shows string.                 ...
Read more

SQL Concepts-1

-
Image
              In this blog we are going to understand the important and basic concepts of SQL... Q. How to  create a SQL server database  connection and new database? 1. Firstly open SSMS and connect with server also ensure SQL Server(MSSQLSERVER) is in running mode - 2. Create a new database -                                                              3. Create a table - // U need to refresh whenever u add a new table            Q. What is primary key and foreign key in SQL server? Primary Key :  Primary key is a column having unique record. Primary key cannot have NULL values. It is used to uniquely identify records in relational database. Only one primary key is allowed in a table. When we delete or update records, data integrity happens. Foreign Key...
Read more

ASP .NET Session Management

-
Image
  Q. Why http protocol is called as stateless ? HTTP protocol is called as stateless because the http server not keeps the record of any information. When client requests to server, every time request treated independently(it not checks whether it requested before or not) by server. We can also say that when client ends the process of any task the connection between server and browser also terminated. In below example we can see that after accessing browser when we again access the value of i starts again from 0.                                                   Stateless Protocol : Stateless protocols are the protocols in which client sends request to server and server response back to client based on current state.  Server not require to maintain information of each client with multiple requests. Some important features of stateless protocol are :...
Read more