Can SQL Server Use Multiple Indexes for the Same Query?

indexperformancequery-performancesql server

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)

Best Answer

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

Related Question