Benefit to additional index with key column moved to includes

Question

Say I have an Index Foo with:

Keys [A, B, C] and Included columns (D, E)

Is there any performance reason to have an additional index Bar with:

Keys [A, B] and Included columns (C, D, E)

My assumption is that a non-needed key at the end would be treated the same as an included column.

Answer 1

IMO, first we have to understand

1. How optimizer work ?
2. Optimizer will choose choose which index and WHY ?

As we know Optimizer make Cost base query plan. if query plan is not Trivial or Simple then it must be Cost based Optimization.

Example,

CREATE TABLE Test (
   id int identity(1,1) primary key
    A VARCHAR(50)
    ,B VARCHAR(50)
    ,C VARCHAR(50)
    ,D VARCHAR(100)
    ,E VARCHAR(100)
    )

INSERT INTO Test
WITH (TABLOCK) (
        A
        ,B
        ,C
        ,D
        ,E
        )
SELECT CONCAT (
        REPLICATE('A', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('B', 30)
        ,(rn / 5)
        )
    ,CONCAT (
        REPLICATE('C', 30)
        ,(rn / 5)
        )
    ,REPLICATE('D', 100)
    ,REPLICATE('E', 100)
FROM (
    SELECT TOP (1000000) ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
    FROM sys.objects A
        ,sys.objects B
        ,sys.objects C
    ) t4

CREATE NONCLUSTERED INDEX nc1_ABC ON Test (
    A
    ,B
    ,C
    ) include (
    D
    ,E
    )

CREATE NONCLUSTERED INDEX nc2_AB ON Test (
    A
    ,B
    ) include (
    C
    ,D
    ,E
    )

In my example Index size of nc1_ABC will be more than nc2_AB. WHY ?

So if my query is,

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc1_ABC index.It will not use nc2_AB.

But if my query is

SELECT A
        ,B
        ,C
        ,D
        ,E
    FROM test
    WHERE A = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA14372'
        AND B = 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB14372'
        --and c='CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC14372'

Optimizer will choose nc2_AB. WHY ?

Because optimizer will compare the price of choosing nc2_AB and nc1_ABC.Optmizer will see that it has to find all record based on filter A and B, that is can do with both index but index size of nc2_AB is less than nc1_ABC.So it will be use.

Is there any performance reason to have an additional index Bar with:

So it depend upon example and how frequently the query is use and how important it is to get those record quickly.

If all those are correct then additional index Bar is require, else index Foo is good enough for both query.

It really depend upon example and situation.