In this post, we will discuss how we can find the difference in retrieving data with and without using Index in sql server 2008. Also, you can check out my previous posts on:
Follow the below steps:
Create a table with the name Employee2 from the table Employee1 as follow.
Select * into Employee2 from Employee1
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
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)
Create a clustered index on DeptNo column of Employee2 table as follows.
Create clustered index DeptNoidx on Employee2 (DeptNo)
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)