Where to place non clustered index

Question

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

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
)
go

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

update SomeTable
set 
    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:

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)
go

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:

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