none
SQL -Usage of Non Clustered Index RRS feed

  • Question

  • I have 2 Index:

    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
        [Column1] ASC,
        [Column2] ASC
    )
    
    CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
    (
        [Column3] asc
    )

    Now I have 2 update:

    Update TableA SET Column3='' where Column1=''

    the above Update was using Index 1st and then Key lookup to clustered Index

    Update TableA SET Column3='' where Column3=''

    This update was using 2nd index

    Based on the Update I thought I can delete 2nd index and update the first one with

    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
    [Column1] ASC,
    [Column2] ASC)INCLUDE(Column3 asc
    )

    So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.

    Then I tried

    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
    [Column1] ASC,
    [Column2] ASC,
    [Column3] asc
    )

    But still for second update it ask me to create a new non clustered index.

    But why it ask me to create new, because what I understand is Column1, Column2, Column3 will be present in Root level for non clustered Index


    Monday, July 22, 2019 7:00 AM

Answers

  • Yes , I wrote you above that it should use the index, you can test it no?

    >>>And does order of columns in INCLUDE matters?like instead of x,y,z can i have >>>z,y,x?

    Not really


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 23, 2019 5:47 AM
    Answerer
  • Hi Khushboo dubey,

     

    Thank you for your issue

     

    I will show you an example to explain your issue. I think one possibility is that the issue may be related to the data itself.

     

    I think it might be easy to understand INCLUDE. When you use it ,Column3 is not an index. So when you create first index and then execute the second query , it will suggest you to create a new index .

     

    When you create an index , your data will be like following script . You will find that Column3 might out of order. So it will still suggest you to create an index on Column3 .

     
    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
    [Column1] ASC,
    [Column2] ASC,
    [Column3] asc
    )
    /*
    Column1    Column2     Column3     
    1                  1                   1
    1                  1                   2
    1                  1                   3
    1                  2                   1
    1                  2                   2
    1                  3                   1
    */ 
    
    

    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.

    Tuesday, July 23, 2019 5:53 AM

All replies

  • So after deleting 2nd index and adding it as include did 2nd update command got slow or fast or was of same speed ? 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 22, 2019 8:17 AM
  • it was slow and it asked to create NON clustered index column column 3
    Monday, July 22, 2019 8:19 AM
  • The index is used  but it PERFORMS SCAN and show missing index hint, am I right?

    In Sql Server the order of the columns in a composite index does matter on how a query against a table will use it or not. A query will use a composite index only if the where clause of the query has at least the leading/left most columns of the index in it


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 22, 2019 9:23 AM
    Answerer
  • The index is used  but it PERFORMS SCAN and show missing index hint, am I right? : yes

    I am using index

    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
    [Column1] ASC,
    [Column2] ASC,
    [Column3] asc
    )

    So in this case should I keep my  index :

    CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA] ( [Column3] asc )

    which is used to perform Update Command where column3=column3

    Monday, July 22, 2019 9:27 AM
  • Correct

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 22, 2019 9:34 AM
    Answerer
  • So suppose we have a query:

     select x,y,z from tableA where a=a and b=b and d=d

    Then we can have NON clustered Index something like below right?

    CREATE NON CLUSTERED INDEX IX_1 on tableA

    ( a asc,b asc,d asc)

    include(x,y,z)

    and Will the query use if we have  index  something like below?

    CREATE NON CLUSTERED INDEX IX_1 on tableA

    ( b asc,d asc,a asc,c asc)

    include(x,y,z)



    Tuesday, July 23, 2019 5:08 AM
  • >>>Then we can have NON clustered Index something like below right?

    >>>CREATE NON CLUSTERED INDEX IX_1 on tableA

    >>>( a asc,b asc,d asc)

    >>>include(x,y,z)

    Correct. SQL Server will use that index for 

    select x,y,z from tableA where a=a and b=b and d=d

    >>>and Will the query use if we have  index  something like below?

    >>>CREATE NON CLUSTERED INDEX IX_1 on tableA

    >>>( b asc,d asc,a asc,c asc)

    >>>include(x,y,z)

    Yes, it should use IX_1  forselect x,y,z from tableA where a=a and b=b and d=d


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 23, 2019 5:35 AM
    Answerer
  • And if we have select x,y,z from tableA where a=a and b=b and d=d

    will sql use below index?

    CREATE NON CLUSTERED INDEX IX_1 on tableA

    ( b asc,d asc, c asc)

    include(x,y,z)

    And does order of columns in INCLUDE matters?like instead of x,y,z can i have z,y,x?

    Tuesday, July 23, 2019 5:40 AM
  • Yes , I wrote you above that it should use the index, you can test it no?

    >>>And does order of columns in INCLUDE matters?like instead of x,y,z can i have >>>z,y,x?

    Not really


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 23, 2019 5:47 AM
    Answerer
  • Thanks Uri..I tested it but wanted to double confirm as with Index we have so many scenarios
    Tuesday, July 23, 2019 5:49 AM
  • Hi Khushboo dubey,

     

    Thank you for your issue

     

    I will show you an example to explain your issue. I think one possibility is that the issue may be related to the data itself.

     

    I think it might be easy to understand INCLUDE. When you use it ,Column3 is not an index. So when you create first index and then execute the second query , it will suggest you to create a new index .

     

    When you create an index , your data will be like following script . You will find that Column3 might out of order. So it will still suggest you to create an index on Column3 .

     
    CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
    (
    [Column1] ASC,
    [Column2] ASC,
    [Column3] asc
    )
    /*
    Column1    Column2     Column3     
    1                  1                   1
    1                  1                   2
    1                  1                   3
    1                  2                   1
    1                  2                   2
    1                  3                   1
    */ 
    
    

    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.

    Tuesday, July 23, 2019 5:53 AM