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

InfoPath alternatives for form designing SharePoint
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:
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)

Similar SharePoint 2013 Tutorials


About Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

View all posts by Bijay Kumar →

Leave a Reply