none
index uses RRS feed

  • Question

  • 


    TableA has indexes as below 

    ColA -- nonclustered index

    ColB,ColC -- Clustere index

    now when execute above query and check execution plan, none of the query using clustered index, all are using non clustered index, how? 


    SQL Server DBA

    Sunday, December 18, 2016 2:58 AM

Answers

  • In addition to Elmozamil's post: in a non-clustered index, the clustered index keys serves as row locators to get to the data page. That is, in a non-clustered index the keys of the clustered index are always present, which means that to run you three queries, all data can be retrieved from the non-clustered index.

    Sunday, December 18, 2016 11:34 AM
  • Hi,

    As all of your where conditions are using the ColA = Value (23) and colA has nonclusted index so it will use the nonculster index.

    For cluster index you need to put it in where condition so it will be used. 

    If you use it in the select statement as you are trying to do I believe the noncluster index with include option is better.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Sunday, December 18, 2016 6:31 AM
  • TableA has indexes as below 

       ColA -- nonclustered index
       ColB, ColC -- Clustered index

    now when execute above query and check execution plan, none of the query using clustered index, all are using non clustered index, how?

    The definition of which index to use in the execution plan depends on several factors and not only on the columns that are in the WHERE clause.

    Are the ColB and ColC columns as included columns in the nonclustered index? The absence of the Key Lookup operator makes me deduce that the nonclustered index is coverage.

    CREATE nonclustered INDEX index_name
       on tableA (colA)
      include (ColB, ColC);
      

        José Diz     Belo Horizonte, MG - Brasil


    Sunday, December 18, 2016 11:25 AM

All replies

  • Hi,

    As all of your where conditions are using the ColA = Value (23) and colA has nonclusted index so it will use the nonculster index.

    For cluster index you need to put it in where condition so it will be used. 

    If you use it in the select statement as you are trying to do I believe the noncluster index with include option is better.

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid


    MCSE Data Platform MCITP: SQL Server 2008 Administration/Development
    MCSA: SQL Server 2012/2014
    MCTS: SQL Server Administration/Development
    MyBlog

    Sunday, December 18, 2016 6:31 AM
  • TableA has indexes as below 

       ColA -- nonclustered index
       ColB, ColC -- Clustered index

    now when execute above query and check execution plan, none of the query using clustered index, all are using non clustered index, how?

    The definition of which index to use in the execution plan depends on several factors and not only on the columns that are in the WHERE clause.

    Are the ColB and ColC columns as included columns in the nonclustered index? The absence of the Key Lookup operator makes me deduce that the nonclustered index is coverage.

    CREATE nonclustered INDEX index_name
       on tableA (colA)
      include (ColB, ColC);
      

        José Diz     Belo Horizonte, MG - Brasil


    Sunday, December 18, 2016 11:25 AM
  • In addition to Elmozamil's post: in a non-clustered index, the clustered index keys serves as row locators to get to the data page. That is, in a non-clustered index the keys of the clustered index are always present, which means that to run you three queries, all data can be retrieved from the non-clustered index.

    Sunday, December 18, 2016 11:34 AM
  • Here is a few question arise in my mind,

    first question is if non clustered index hold row locator only that mean, to get actual data ColB and ColC it has to travel to the cluster index page?? which i can not see in execution plan.

    second question is i was assuming key look up in either first query or third query, but none of the query using key look up then in which situation key lookup is occurred ?


    SQL Server DBA

    Sunday, December 18, 2016 2:37 PM
  • "Zeal", follows the included columns and key lookup operator demonstration.

    (1) Considering the following data structure:

    -- code #1
    CREATE TABLE tableA (colA int, colB date, colC varchar(20), colD time(0),
                         constraint I1 primary key (colB, colC));
    go
    
    CREATE nonclustered INDEX I2 on tableA (colA);
    go
    


    (2) When executing the two queries from code #2 ...

    -- code #2
    SELECT colA, colB, colC
      from tableA 
      where colA = 23;
      
    SELECT colA, colB, colC, colD  
      from tableA 
      where colA = 23;

     
    ... we have the following execution plans:

     

     

    (3) Below, we have included a few rows in the table:

    -- code #3
    INSERT into tableA (colA, colB, colC, colD)
      SELECT n, DateAdd(day, +n, '20120112'), replicate('A', ((n % 20) +1)), null
        from dbo.Nums 
        where n <= 1000;
    
     

    When we reexecute code #2, we have the following execution plans:


     Note the presence of the key lookup operator ("Pesquisa de Chave").

    (4) Next we change the nonclustered index to contain column colD as included column:

    -- code #4
    DROP INDEX I2 on tableA;
    CREATE nonclustered INDEX I2 on tableA (colA) include (colD);  
    go

    When we reexecute code #2, we have the following execution plans:


        José Diz     Belo Horizonte, MG - Brasil


    Sunday, December 18, 2016 4:16 PM
  • if non clustered index hold row locator only that mean, to get actual data ColB and ColC it has to travel to the cluster index page??

    For B-tree tables (clustered index), the row locator (in nonclustered index) is composed of the contents of ColB and ColC columns.

    If the values of the ColB and ColC columns are present in the nonclustered index, then it is not necessary to go in the table (clustered index) to get the values.


        José Diz     Belo Horizonte, MG - Brasil



    • Edited by José Diz Sunday, December 18, 2016 4:38 PM
    Sunday, December 18, 2016 4:29 PM
  • first question is if non clustered index hold row locator only that mean, to get actual data ColB and ColC it has to travel to the cluster index page?? which i can not see in execution plan.

    second question is i was assuming key look up in either first query or third query, but none of the query using key look up then in which situation key lookup is occurred ?

    The non-clustered index leaf nodes contain the non-clustered index key, clustered index key (row locator), and included columns. No additional key lookup into the clustered index is needed when the query needs only the aforementioned columns (covering index) because the leaf nodes contain all the data needed by the query.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, December 18, 2016 4:36 PM
  • Jose,

    this is amazing exploitation, can you please conclude over above representation i can conclude as below.

    non clustered index stores following things

    1. its own key values (in our case ColA values)

    2. pointer to point on actual page

    3. row locator (it consist of values of clustered index).

    Key look up scenario is completely depends on data because you executed same query before and after data insert, after data insert key look up is occurring, that mean key look up depends on data ?


    SQL Server DBA

    Monday, December 19, 2016 4:42 AM
  • non clustered index stores following things

    1. its own key values (in our case ColA values)

    2. pointer to point on actual page

    3. row locator (it consist of values of clustered index).

    No, this is not correct. At the leaf level, you find the key values, the values of any included columns and the row locator.

    If the table has a clustered index, the row locater is the keys of the clustered index. There is no separate pointer to the page.

    If the table is a head, the row locater is indeed the address to the page, that is a pointer.

    And because the keys of the clustered are present in the leaf level of the index, the queries you had can be satisfied by the non-clustered index alone.

    Add a fourth column to the table and don't make that column part of any index. If you add this to the SELECT list, you will now get a key lookup.

    Monday, December 19, 2016 8:43 AM
  • 

    Custered index : ColB

    non Clustered index: ColA

    what is the reason i am not getting key look up in here?


    SQL Server DBA

    Tuesday, December 20, 2016 6:05 AM
  • Hi Zeal DBA

    I have made a test with following code and result would be found in the comments.

    CREATE TABLE tableA (colA int, colB date, colC varchar(20), colD time(0),
                         constraint I1 primary key (colB));
    go
    
    CREATE nonclustered INDEX I2 on tableA (colA);
    go
    
    --This following would happen "Clustered Index Scan(Clustered)"
    select colA, colB,colC,colD     from tableA where colA = 1
    select colC,colD     from tableA where colA = 1
    select colA, colC    from tableA where colA = 1
    select colB, colC    from tableA where colA = 1
    
    --This following would happen "Index Seek(NonClustered)"
    select colA, colB    from tableA where colA = 1
    select colA    from tableA where colA = 1
    select colB    from tableA where colA = 1

    About why your above query happens “Clustered Index Scan”, you could have a look at this similar thread. In this thread, we could find following statement.

    When SQL Server is looking for your data probably one of the largest things which will make SQL Server switch from a seek to a scan is when some of the columns are you looking for are not included in the index you want it to use.

    About why the “Index Seek(NonClustered)" happens, you could have a look at this thread which says “Index Seek will be used only if conditions from WHERE or ON clauses are the first in the list of columns of the Index. Also, this fields must be selective which means that the condition must filter just a little percent or rows from a table. Also, you don't need to include column that is part of the clustered index into list of columns of a nonclustered index, because Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table.“

    Best Regards,

    Albert Zhang


    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, December 20, 2016 7:55 AM
  • Because the optimizer estimates that it's more expensive to find all matching rows in the non-clustered index and then look up the rows in the data page than to scan the clustered index.

    A choice between NC index + key lookup or CI/table scan is a classic trade-off an optimizer has to make. When reading a few rows the first operation is much more efficient, but for reading all rows it's extremely ineffecient.

    Think of it this way: you open a book and look in the index to find where it talks about emperor Nero. You find he is discussed on page 56-58, so you go to these pages and read. Next book you open, there are a long list of pages. You find that is just to tiresome to flip to all those pages, so you decide to read the entire book.

    Tuesday, December 20, 2016 8:19 AM
  • (...)  over above representation I can conclude as below.
       non clustered index stores following things
         1. its own key values (in our case ColA values)
         2. pointer to point on actual page
         3. row locator (it consist of values of clustered index).

    After executing code #1 above, we have in the nonclustered index: 
        1. its own key: ColA
        2. row locator: ColB and ColC.

    After executing code #4 above, we have in the nonclustered index:
        1. its own key: ColA
        2. row locator: ColB and ColC
        3. included column: ColD.

     

    To understand how indexes work in SQL Server, I suggest reading:


    With respect to the execution plans, you can get a free copy of e-book SQL Server Execution Plans, by Grant Fritchey.


        José Diz     Belo Horizonte, MG - Brasil


    • Edited by José Diz Tuesday, December 20, 2016 4:45 PM
    Tuesday, December 20, 2016 4:33 PM