Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and which columns should i use in incluse list. I am using SQL Server 2008/R2

  • 5
    Please post your DDL, as well as your query. Jul 20, 2012 at 15:03
  • 2
    When index tuning, please don't guess. :)
    – Jon Seigel
    Jul 20, 2012 at 15:14

1 Answer 1


In there interest of showing a generalized example until we see your particular DDL and query, take the below as a basic example:

create table SomeTable
    id int identity(1, 1) not null
        primary key clustered,
    AnotherInt int null,
    SomeData nvarchar(1024) null

insert into SomeTable(AnotherInt, SomeData)
values(null, null)
go 1000

update SomeTable
    AnotherInt = id * 3 + 152,
    SomeData = 'My ID value is ' + cast(id as nvarchar(16))

So we have a test table, SomeTable, with a clustered index. Execute the below query:

select SomeData
from SomeTable
where AnotherInt < 200

This causes a Clustered Index Scan, like you are seeing:

enter image description here

So analyzing the query, we have the column AnotherInt that is in the WHERE clause and being searched on. We are also retrieving the SomeData column, so to prevent a key lookup in the clustered index (or the optimizer may even just use a Clustered Index Scan again), we'll have SomeData as an INCLUDE column:

create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt) 
include (SomeData)

Now, executing the same query above:

select SomeData
from SomeTable
where AnotherInt < 200

SQL Server will utilize that non-clustered index to return the data. It is a covering index, because it won't need to do a lookup on the clustered index for the remaining data:

enter image description here

Through creating a prudent NCI, we have now eliminated the CI Scan in lieu of an Index Seek.

  • @aasim.abdullah No problem at all. If you post your CREATE TABLE batch and any other surrounding DDL (i.e. CREATE INDEX ...) as well as your query, I can use your particular situation and go from there. Otherwise, if this answers your question, please consider upvoting and marking this answer as accepted. Jul 20, 2012 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.