5

This question might have been asked before, but I tried searching with this term "can SQL Server use two indexes for same query" and it yielded no results.

Assume you have the below query:

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab 
group by col1a 
having count (distinct colb)>1)

And you have the below index:

create index nci on #ab(colb)
include(col1a)

This is the execution plan, and Paste The Plan link as well.

NUTS

Bottom part of plan is for below query:

Select col1a from #ab 
group by col1a 
having count (distinct colb)>1

Table scan/top part of plan is for below query:

select col1a,colb
from #ab
where col1a in (

Question:

If I have an index like below:

create index nic_toppart on #ab(Col1a,colb)

Will it be chosen for top part of plan?

In summary I meant:

Can SQL Server use index nci for grouping part/bottom part of plan and use index nic_toppart for top part of the plan

Is this possible?

My tests show it can only choose one.

Below is test data. Please let me know if I am unclear.

create table #ab
(
col1a int,
colb char(2)
)

insert into #ab
values
(1,'a'),
(1,'a'),
(1,'a'),
(2,'b'),
(2,'c'),
(2,'c')

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab 
group by col1a 
having count (distinct colb)>1)


create index nci on #ab(colb)
include(col1a)

create index nci_p on #ab(col1a,colb)
9

Yes, but not with the indexes you've chosen.

If I create these indexes:

CREATE INDEX ix_top ON #ab (col1a) INCLUDE (colb);

CREATE INDEX ix_bottom ON #ab (colb, col1a);

I get back this plan:

NUTS

And that's without having to use multiple index hints.

With a slightly different index:

CREATE INDEX ix_mindyourbusinessypercube ON #ab (col1a, colb);

The plan changes to use just that one index, though performance ramifications are difficult to surmise given the limited test data. I leave that as an exercise to ypercube the reader.

NUTS

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.