none
SQL: Importance of INCLUE in NON CLUSTERED INDEX RRS feed

  • Question

  • I am working on Consolidation INDEX. I have 3 index on a table

    Create NONCLUSTERED INDEX IX1 on TableA(a asc,b asc) INCLUDE(U,V,X,Y,Z)
    Create NONCLUSTERED INDEX IX2 on TableA(a asc,b asc) INCLUDE(K,L,X,Y,Z)
    Create NONCLUSTERED INDEX IX3 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z)

    So as the composite key is same and the order is also same. I thought we can have 1 index rather than 3

     Create NONCLUSTERED INDEX IX4 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z,K,L)

    So , i have 2 Question:
    1 : Will creating new index for the 3 will work?
    2 : Some developers tell me that difference in include statement as now I have (U,V,X,Y,Z,K,L) all in one can have significant difference in performance so ,I can't club them in 1?



    Wednesday, July 24, 2019 5:38 AM

Answers

  • Get them to explain what they think "significant difference in the performance" means to them.

    Your approach is sound and will help overall as any updates to a row will then only have to update 1 further index value instead of 3 - that is a saving immediately. The total space consumed will be less as again you have reduced multiple copies of INCLUDED columns down to 1.

    I can't see any significant disadvantage. The only concern I would have is if the INCLUDED columns were very large CHAR/VARCHAR columns which may consume a lot of space.

    If the index then becomes a covering index for your queries then performance will significantly IMPROVE, not degrade.


    Martin Cairney SQL Server MVP

    Wednesday, July 24, 2019 6:09 AM

All replies

  • It depends - as everything does.

    If we first of all consider the indexes without the INCLUDED columns then consolidating the indexes based on the key columns is correct. This will save the space of the 3 indexes into just 1.

    Now, when we add the INCLUDE, this stores a copy of the values from those columns along with the index key column values. This does make the index wider at the leaf level only.

    However, as you are still consolidating the indexes, the overall space consumed will still be less than 3 separate indexes.

    The number of reads to find the value you want is still optimal since the higher levels of the index don't contain the INCLUDED values - and when the query finds the rows you want, then it doesn't have to do a lookup to the Clustered index to get those values as well.

    As long your queries are covered by the new consolidated index then you will expect to see overall improvements in performance, not degradation.


    Martin Cairney SQL Server MVP

    Wednesday, July 24, 2019 5:57 AM
  • Thanks Martin,

     I want to consolidate and u can see for the consolidated Index I have added few columns in INCLUDE.

    I want to know as some developer complain "only one column difference in include statement and all the rest the same in the index could make significant difference in the performance"?

    example :

    INCLUDE(U,V,X,Y,Z)

    INCLUDE(K,L,X,Y,Z)

    and if Consolidated have INCLUDE(U,V,X,Y,Z,K,L)

    as I understand that index performance is more related to Key columns rather than Included column
    Wednesday, July 24, 2019 6:03 AM
  • Get them to explain what they think "significant difference in the performance" means to them.

    Your approach is sound and will help overall as any updates to a row will then only have to update 1 further index value instead of 3 - that is a saving immediately. The total space consumed will be less as again you have reduced multiple copies of INCLUDED columns down to 1.

    I can't see any significant disadvantage. The only concern I would have is if the INCLUDED columns were very large CHAR/VARCHAR columns which may consume a lot of space.

    If the index then becomes a covering index for your queries then performance will significantly IMPROVE, not degrade.


    Martin Cairney SQL Server MVP

    Wednesday, July 24, 2019 6:09 AM
  • Thanks Martin 
    Wednesday, July 24, 2019 6:15 AM
  • Hi Khushboo dubey,

     

    Thank you for your issue .

     

    For first question , I am not sure if it will work . I think it depends your actual environment.

     

    For second one, I agree that if you create an index INCLUDE(U,V,X,Y,Z,K,L) and it might have a poor performance.

     

    When you create an index , I think that it might be related to your actual query. In general, which columns will you use as filters or use in WHERE clause ? Please try to create an index on these column instead of trying to create an index and including all or most columns.

     

    Also , please find out more about the usage of INCLUDE. If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause, you might need to use INCLUDE .The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree. INCLUDE columns are not key columns in the index, so they are not ordered. This means it isn't really useful for predicates, sorting etc as I mentioned above. However, it may be useful if you have a residual lookup in a few rows from the key column(s). There is an article and I think it will help you a lot. SQL Server Indexes with Included Columns

     

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 6:18 AM