Need clarification on SQL Server index selection

Question

We have an existing table structure as below

Table Name: Provider #

Columns:

Provid
EntityId
SpecialityCode
ProvType
FedId
FullName
Status

In this table, ProvId is the primary key. So we have a clustered index on ProvId.

Apart from this, we have two composite nonclustered indexes as below

CREATE NONCLUSTERED INDEX [Provider0] 
ON [dbo].[provider] ([fedid] ASC, [provid] ASC, [entityid] ASC, [fullname] ASC, [status] ASC)

CREATE NONCLUSTERED INDEX [XIE3Provider] 
ON [dbo].[provider] ([fedid] ASC, [entityid] ASC, [provtype] ASC, [fullname] ASC, [provid] ASC, )

Query 1:

SELECT provid 
FROM provider 
WHERE FedId = '123'

For query 1, SQL Server uses XIE3Provider index for seek operation.

Query 2:

SELECT provid, status 
FROM provider 
WHERE FedId = '123'

For query 2, SQL Server uses the Provider0 index for seek operation.

Could you please provide more details as to how SQL Server switches the index selection for this case?

Lukasz Szozda · Accepted Answer · 2018-05-21T14:12:41.230

SQL Server's query optimizer tries to utilise covering index:

A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.

Select provid, status from provider where FedId  = '123';

Filter: FedId

Select: provid, status.

So it chose to use:

CREATE NONCLUSTERED INDEX [Provider0] ON [dbo].[provider]
(
    [fedid] ASC,
    [provid] ASC,
    [entityid] ASC,
    [fullname] ASC,
    [status] ASC
);

You could use INCLUDE clause:

CREATE NONCLUSTERED INDEX [Provider0] ON [dbo].[provider]
(
    [fedid] ASC,
    [provid] ASC,
    [entityid] ASC,
    [fullname] ASC,
) INCLUDE (status);

Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

score 0 · Answer 2 · answered May 21 '18 at 14:10

Where possible SQL will ignore the Table and retrieve all relevant data from the Index. Because the Index is smaller than the Table and reading the Index will require less IO

Status, provid and FedId are in the Provider0 Index so SQL uses this index to reduce IO.

Need clarification on SQL Server index selection

2 Answers2