Non Clustered Index Scan Vs Non Clustered Index Seek

Introductions 

The concept of non clustered index is like the index page in the behind of a book. Consider your C# book, lets say you are trying to search a topic named "delegates" then what you will do ?

You will go to the index page and search there for this " Delegates" value and then you will move to the corresponding page.

Consider if the index page was not there then you have to scan all the pages of the book in order to find the particular page.

Something exactly like the above scenario happens in the database. If you create an index page ( here it is called as Non Clustered Index ) in the table then it will minimize the table scans hence providing you better an execution plan.

Behind The Scene 

Before we proceed you need the AdventureWorks2008R2 Database installed in your SQL Server 2008 R2. Download the database here from Codeplex Download

And download the query presenting all the things I am going to explain here from the below download link.

   Download Source Code Here

Now, Just create a new table with the bellow query, upon which we will experiment through out this article.

-- CREATE A TABLE TO EXPERIMENT ON THE NON CLUSTERED INDEX SCAN AND SEEK
 
SELECT * INTO My_DemoSalesOrderDetails
FROM [Sales].[SalesOrderDetail]

This query will create a new table named My_DemoSalesOrderDetails in the AdventureWorks Database. Now just add the primary ke constraint to the newly created table by executing th bellow query.

-- ADD THE PRIMARY KEY CONSTRAINT TO THE NEWLY CREATED TABLE.

ALTER TABLE My_DemoSalesOrderDetails
ADD CONSTRAINT [PK_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED
(
  [SalesOrderID] ASC,
  [SalesOrderDetailID] ASC
)

Now before moving forward for any further demonstration we will set the statistics IO on so that we can look into some important parameters. and also select the execution plan ( CTRL + M ) .

-- SET THE STATICS IO ON

SET STATISTICS IO ON
GO

Fine, we are doing well. Here is the time to add the "Non Clustered index" to the table In order to add the index just run the below query, here we are adding 2 columns named [OrderQty] and the [ProductID] as the non clustered index.

-- CREATE INDEX WITH OrderQty and ProductID

CREATE NONCLUSTERED INDEX [ IX_MySalesOrderDetail_OrderQty_ProductID]
ON My_DemoSalesOrderDetails
([OrderQty], [ProductID])
GO

Non Clustered Index Scan:

      Good, now just try to select * form the tabe now.

-- SELECT * FORM THE TABLE

SELECT SalesOrderID, SalesOrderDetailID,ProductID, OrderQty
FROM My_DemoSalesOrderDetails 

you will see in the message tab that a logical read of 257 has occurred, and in the Execution plan tab you can find an Index Scan has encountered.

Non Clustered Index Seek:

Now just add a where cluse to the select statement and see the message tab and the execution plan tab.

-- SELECT * FORM THE TABLE WITH A WHERE CLAUSE
SELECT SalesOrderID, SalesOrderDetailID,ProductID, OrderQty 
FROM My_DemoSalesOrderDetails 
   WHERE OrderQty = 1

In the message tab you will find a logical read of 160, and in the execution plan tab you will be able to see that an Index Seek operation has occurred.

Up to now, I hope you got the idea behind the non-clustered index scan and the non-clustered
seek.

But one thing I want to say here if you slightly change the where clause by adding productID over there instead of OrderQty, then you will see a logical read of around 1496 occurred in the message tab.

So in order to decrease the logical reads you can add another non-clustered index to the table with productID first.

-- CREATE INDEX WITH ProductID and OrderQty 

CREATE NONCLUSTERED INDEX [ IX_MySalesOrderDetail_ProductID_OrderQty]
ON My_DemoSalesOrderDetails
([ProductID],[OrderQty])
GO

now, if you execute the previous query then you will find a logical read of just 9. That's it, From the above we can conclude that a table can have multiple non clustered indices.

Note :

While using SQL Server we should think about the query optimization, I mean the efficiency of the SQL query in terms of CPU cost, IO cost and execution time.

So It is a better idea to implement the clustered indexing ( Primary Key ) in the tables, so that in each case a request for selecting any document goes to the server, it does not need to scan all the records present instead the qualifying ones.

Happy Coding...