patternsqlMinor
Where to place non clustered index
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
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:
So we have a test table, SomeTable, with a clustered index. Execute the below query:
This causes a Clustered Index Scan, like you are seeing:
So analyzing the query, we have the column AnotherInt that is in the
Now, executing the same query above:
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.
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 < 200This 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)
goNow, executing the same query above:
select SomeData
from SomeTable
where AnotherInt < 200SQL 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 < 200create nonclustered index IX_SomeData_AnotherInt_SomeData
on SomeTable(AnotherInt)
include (SomeData)
goselect SomeData
from SomeTable
where AnotherInt < 200Context
StackExchange Database Administrators Q#21240, answer score: 4
Revisions (0)
No revisions yet.