none
How non clustered Index Work in SQL RRS feed

  • Question

  • Hi,

    I have a table with 2 Index

    • Clustered index on Column (ID)
    • Non Clustered Index on column (CustomerId and DiagTime

    So when i run  query and see execution plan i can see it use Clustered index 

        SELECT   DISTINCT customerid, 
                                diagtime AS DateTime,
                                eventuid, 
                                meterid  AS AssetId, 
                                areaid, 
                                2001     AS EventCode, 
                                timetype1, 
                                timetype2, 
                                timetype3, 
                                timetype4, 
                                timetype5, 
                                '2'      AS [Description], 
                                NULL     AS TechnicianKeyID 
                FROM   meterdiagnostic WITH(nolock ) 
                WHERE  customerid = @CustomerID  and diagtime BETWEEN @StartDate AND @EndDate 
    So in this Case I think as columns in select statement are not present in Non Clustered index so it uses clustered index because other wise it has to use non clustered index and again go to clustered index to fetch other non key columns.So it find the optimized way is use clustered index

    But when i run below query Optimizer make use of NON clustered index

        SELECT   DISTINCT ID
                FROM   meterdiagnostic WITH(nolock ) 
                WHERE  customerid = @CustomerID  and diagtime BETWEEN @StartDate AND @EndDate 
    But here also ID column is not present in NON CLustered index then why it make use of non Clustered index






    Wednesday, January 18, 2017 1:49 AM

Answers

  • Because the ID is part of the PK and is "stored" in the leaf level of the index.

    So the index would really look like this

    create index blah on meterdiagnostic(CustomerID, diagTime) include(ID)

    Wednesday, January 18, 2017 2:11 AM

All replies

  • Because the ID is part of the PK and is "stored" in the leaf level of the index.

    So the index would really look like this

    create index blah on meterdiagnostic(CustomerID, diagTime) include(ID)

    Wednesday, January 18, 2017 2:11 AM
  • As in non clustered index 

    

    Where In my leafnode it will be CustomerId and DiagTime

    And In datapage Complete table with Primary key(ID) will be arranged in a order

    So Does in this scenario optimizer go From leafnode to Datapage to fetch ID

    Or It would be by default that if we create a non clustered index Leafnode will have (Columns +Primary key)



    Wednesday, January 18, 2017 2:20 AM
  • In the nonclustered index pages.

    If you were to open them up you might see

    11,'2017-01-01';11,'2017-01-02'.....1111,'2018-12-01',...

    in the datapages area you will see the cluster key values

    1,2,3,4.....

    Each page will have the ranges of data that will fill up that range.

    This image here is pretty good showing how the values are distributed - but it does not show the clustered key values:

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking185.htm


    Wednesday, January 18, 2017 2:43 AM
  • By this diagram in link provided above ,we can say that datapage contain Primary key + other non keys columns

    So in my first query where i have non keys columns in select ,It select clustered index,It can also select Non clustered index and from datapage it can get values for non key columns?

    Wednesday, January 18, 2017 3:16 AM