0

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.

  • 5
    I can think of several reasons why this would be harmful, no reasons why it would be helpful. – James Jenkins Jul 11 at 16:35
  • 5
    I guess you can come up with close to absurd cases. Like C being extremely wide and having another index with it ac included makes the non-leaf smaller (incl are only in leaf). Or extremely frequently modified (incl are not sorted, only stored). But IMO that would be such extremely cases so IMO that second index is redundant (and carries of course overhead). – Tibor Karaszi Jul 11 at 17:24
  • 2
    Note: I have been working with indexes that SQL Server thinks are missing, and you will often end up with recommended indexes on a table that look like: [A], [A] include [B, F], [A, B], [A, B, F] include [C, D], and [A, B] include [N, Q, X, R]. Usually, these five can be covered by and index [A, B, F] include [C, D, N, Q, X, R]. However, if one of the INCLUDE values is large, e.g. VARCHAR[8000] NOT NULL, then having the one index that includes that separate from the others can be more efficient overall. – Laughing Vergil Jul 11 at 17:44
  • Thank you all for verifying my understanding. @LaughingVergil I think that is a good point to keep in mind, although in my case this is not an issue. Much appreciated. – Andrew Hanlon Jul 11 at 20:50
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.

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.