HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Where to place non clustered index

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
clusterednonwhereplaceindex

Problem

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

Solution

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.

Code Snippets

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))
select SomeData
from SomeTable
where AnotherInt < 200
create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt) 
include (SomeData)
go
select SomeData
from SomeTable
where AnotherInt < 200

Context

StackExchange Database Administrators Q#21240, answer score: 4

Revisions (0)

No revisions yet.