Powered by Blogger.

Saturday, February 15, 2014

How to find the difference in retrieving data with and without using index in sql server 2008?



In this post we will discuss how we can find the difference in retrieving data with and witout using Index in sql server 2008. Also you can check out my previous posts on:

- Serialization and Deserialization in Remoting in C#.Net

- What are different types of results in MVC?

- Create datatable at runtime in Asp.Net

Follow below steps:

Step1:
Create a table with the name Employee2 from the table Employee1 as follow.
                                     Select * into Employee2 from Employee1

Step2:
Write the following insert statement to insert rows into the table Employee2 from Employee2 itself.

Repeatedly execute the below statement until you get thousands of rows in the table Employee2.
                                   Insert Employee2 select * from Employee2

Step3:
Write the below select statement on table Employee2 to find the employees working in Department 30.

Select that statement and use the shortcut CTRL+L and you will get the Estimated Execution Plan.

Within the Estimated Execution Plan place the mouse pointer over table Scan icon and note down the Estimated CPU Cost.
                                   Select * from Employee2 where DeptNo=30 (ex:   --0.126235 milisecond)

Step4:
Create a clustered index on DeptNo column of Employee2 table as follows.
                                 Create clustered index DeptNoidx on Employee2 (DeptNo)

Step5:
Write the select statement of table Employee2 again to find the employees working in Department 30, select the statement and use the shortcut CTRL+L  to get Estimated Execution Plan and within the Estimated
Execution Plan place the mouse pointer over clustered index seek icon and note down the Estimated CPU Cost Which will be less than the Estimated CPU Cost of the same statement when there is no index on DeptNo column, which indicates that the data retrieved will be fast with index.
                              Select * from Employee2 where DeptNo=30   (ex: --0.045213)





0 comments

Post a Comment