Benefit to additional index with key column moved to includes

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply