Any benefit in a covering non-clustered index on clustered index columns?

  • I have many lookup tables - parent table has a ChildID and JOINs the Child Table to get a column containing a descriptive value.

    Primary Key is the ChildID.

    These Child / Lookup Tables have 99% (or more!) reads. WRITES are extremely rare.

    1) Is there any benefit in having a Non-clustered index on ChildID with an INCLUDE for the Description column?

    2) what an index with a FILTER in situations where there is a need to exclude ChildTable rows marked as "Disabled"?

    My sample code only has 20 rows, so not sure whether I would be able to see any tangible difference. STATISTIC Scans / etc. look the same, but the Query Plan is clearly using the non-clustered index as appropriate.

    -- Create Table sample

    CREATE TABLE dbo.TEMP_Lookup

    (

    LookupID int NOT NULL

    , LookupDescription varchar(50) NULL

    , LookupIsEnabled bit NOT NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE dbo.TEMP_Lookup ADD CONSTRAINT

    PK_TEMP_Lookup PRIMARY KEY CLUSTERED

    (

    LookupID

    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    CREATE TABLE dbo.TEMP_Master

    (

    MasterID int NOT NULL

    , MasterLookupID int NOT NULL

    , MasterCol1 varchar(50) NULL

    , MasterCol2 bit NOT NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE dbo.TEMP_Master ADD CONSTRAINT

    PK_TEMP_Master PRIMARY KEY CLUSTERED

    (

    MasterID

    ) WITH( PAD_INDEX = OFF, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    INSERT INTO dbo.TEMP_Lookup(LookupID, LookupDescription, LookupIsEnabled)

    SELECT101, 'Desc101', 1 UNION ALL

    SELECT102, 'Desc102', 0 UNION ALL

    SELECT103, 'Desc103', 1 UNION ALL

    SELECT104, 'Desc104', 0 UNION ALL

    SELECT105, 'Desc105', 1 UNION ALL

    SELECT106, 'Desc106', 0 UNION ALL

    SELECT107, 'Desc107', 1 UNION ALL

    SELECT108, 'Desc108', 0 UNION ALL

    SELECT109, 'Desc109', 1 UNION ALL

    SELECT110, 'Desc110', 0 UNION ALL

    SELECT111, 'Desc111', 1 UNION ALL

    SELECT112, 'Desc112', 0 UNION ALL

    SELECT113, 'Desc113', 1 UNION ALL

    SELECT114, 'Desc114', 0 UNION ALL

    SELECT115, 'Desc115', 1 UNION ALL

    SELECT116, 'Desc116', 0 UNION ALL

    SELECT117, 'Desc117', 1 UNION ALL

    SELECT118, 'Desc118', 0 UNION ALL

    SELECT119, 'Desc119', 1 UNION ALL

    SELECT120, 'Desc120', 0

    INSERT INTO dbo.TEMP_Master(MasterID, MasterLookupID, MasterCol1, MasterCol2)

    SELECT201, 101, 'Desc201', 1 UNION ALL

    SELECT202, 102, 'Desc202', 0 UNION ALL

    SELECT203, 103, 'Desc203', 1 UNION ALL

    SELECT204, 104, 'Desc204', 0 UNION ALL

    SELECT205, 105, 'Desc205', 1 UNION ALL

    SELECT206, 106, 'Desc206', 0 UNION ALL

    SELECT207, 107, 'Desc207', 1 UNION ALL

    SELECT208, 108, 'Desc208', 0 UNION ALL

    SELECT209, 109, 'Desc209', 1 UNION ALL

    SELECT210, 110, 'Desc210', 0 UNION ALL

    SELECT211, 111, 'Desc211', 1 UNION ALL

    SELECT212, 112, 'Desc212', 0 UNION ALL

    SELECT213, 113, 'Desc213', 1 UNION ALL

    SELECT214, 114, 'Desc214', 0 UNION ALL

    SELECT215, 115, 'Desc215', 1 UNION ALL

    SELECT216, 116, 'Desc216', 0 UNION ALL

    SELECT217, 117, 'Desc217', 1 UNION ALL

    SELECT218, 118, 'Desc218', 0 UNION ALL

    SELECT219, 119, 'Desc219', 1 UNION ALL

    SELECT220, 120, 'Desc220', 0

    -- ***** TEST 1 *****

    SELECTMasterID, MasterCol1, MasterCol2, LookupDescription

    FROMdbo.TEMP_Master

    JOIN dbo.TEMP_Lookup

    ON LookupID = MasterLookupID

    -- NOT IN THIS TEST AND LookupIsEnabled = 1

    /*

    -- ***** TEST 1 *****

    Table 'TEMP_Lookup'. Scan count 0, logical reads 40

    Table 'TEMP_Master'. Scan count 1, logical reads 2

    |--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))

    |--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))

    |--Clustered Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]PK_TEMP_Lookup[/highlight]])

    , SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])

    ORDERED FORWARD)

    */

    -- ***** TEST 2 *****

    SELECTMasterID, MasterCol1, MasterCol2, LookupDescription

    FROMdbo.TEMP_Master

    JOIN dbo.TEMP_Lookup

    ON LookupID = MasterLookupID

    [highlight="#ffff11"]AND LookupIsEnabled = 1[/highlight]

    /*

    -- ***** TEST 2 *****

    Table 'TEMP_Lookup'. Scan count 0, logical reads 40

    Table 'TEMP_Master'. Scan count 1, logical reads 2

    |--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))

    |--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))

    |--Clustered Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]PK_TEMP_Lookup[/highlight]])

    , SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])

    , [highlight="#ffff11"]WHERE: ([MyDB].[dbo].[TEMP_Lookup].[LookupIsEnabled]=(1))[/highlight]

    ORDERED FORWARD)

    */

    CREATE UNIQUE NONCLUSTERED INDEX IX_TEMP_LookupDescription1 ON dbo.TEMP_Lookup

    (

    LookupID

    )

    INCLUDE

    (

    LookupDescription

    )

    -- NOT IN THIS TEST:WHERE LookupIsEnabled = 1

    WITH( FILLFACTOR = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    -- ***** TEST 3 *****

    SELECTMasterID, MasterCol1, MasterCol2, LookupDescription

    FROMdbo.TEMP_Master

    JOIN dbo.TEMP_Lookup

    ON LookupID = MasterLookupID

    -- NOT IN THIS TEST AND LookupIsEnabled = 1

    /*

    -- ***** TEST 3 *****

    Table 'TEMP_Lookup'. Scan count 0, logical reads 40

    Table 'TEMP_Master'. Scan count 1, logical reads 2

    |--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))

    |--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))

    |--Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]IX_TEMP_LookupDescription1[/highlight]])

    , SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])

    ORDERED FORWARD)

    */

    CREATE UNIQUE NONCLUSTERED INDEX IX_TEMP_LookupDescription2 ON dbo.TEMP_Lookup

    (

    LookupID

    )

    INCLUDE

    (

    LookupDescription

    )

    WHERE LookupIsEnabled = 1

    WITH( FILLFACTOR = 100, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    -- ***** TEST 4 *****

    SELECTMasterID, MasterCol1, MasterCol2, LookupDescription

    FROMdbo.TEMP_Master

    JOIN dbo.TEMP_Lookup

    ON LookupID = MasterLookupID

    [highlight="#ffff11"]AND LookupIsEnabled = 1[/highlight]

    /*

    -- ***** TEST 4 *****

    Table 'TEMP_Lookup'. Scan count 0, logical reads 40

    Table 'TEMP_Master'. Scan count 1, logical reads 2

    |--Nested Loops(Inner Join, OUTER REFERENCES: ([MyDB].[dbo].[TEMP_Master].[MasterLookupID]))

    |--Clustered Index Scan(OBJECT: ([MyDB].[dbo].[TEMP_Master].[PK_TEMP_Master]))

    |--Index Seek(OBJECT: ([MyDB].[dbo].[TEMP_Lookup].[[highlight="#ffff11"]IX_TEMP_LookupDescription2[/highlight]])

    , SEEK: ([MyDB].[dbo].[TEMP_Lookup].[LookupID]=[MyDB].[dbo].[TEMP_Master].[MasterLookupID])

    ORDERED FORWARD)

    */

    DROP TABLE dbo.TEMP_Lookup

    GO

    DROP TABLE dbo.TEMP_Master

    GO

  • The QO will chose the smallest index, so if there's a nonclustered index that's 1 byte smaller than the clustered, that'll be the one chosen. Whether it makes sense to have such a nonclustered index is another matter. Usually it isn't. There are cases where having such a nonclustered index is useful, generally when the table is huge, the query needs to just access one column and the query is incredibly time-sensitive, in that it absolutely may not take 1ms longer than necessary.

    As for a filtered index, test and see, but again you're probably not going to see a huge benefit on small tables. Also consider the problems of matching queries to filtered indexes http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail. Sounds like more-trouble-than-it-is-worth 🙂

    In the main all - but I ought to make a Belt & Braces test to be sure ... - my Lookup Tables are pretty small.

    Today's panic came about because there were two unique columns (one was the clustered index) and the Lookup was on the other column which had overlooked being indexed, so I set about doing a series of tests (once I'd created the non clustered unique index with both INCLUDE and FILTER) to see whether there appeared to be any benefit for a covering index on the Clustered Index key column(s).

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply