Post reply

Clustered and non clustered indexes

  • January 21, 2010 at 10:15 am

    #209352

    Hi

    I have a Foo table which has Id column(non-clustered index,PK) and Date column(clustered index) .

    When i am running SELECT COUNT(*) from Foo WHERE Date BEETWEEN '20090101' and '20100101'

    query SQL Server uses non-clustered index scan.My question is why it is not using clustered index (index seek) ?

    (I made Foo table simple and i have SQL Server 2005)

    Thank You

  • January 21, 2010 at 10:48 am

    #1106841

    Most likely because it's figured that it will read fewer pages if it scans the nonclustered index. The NC index has the clustering key in it, so it can just scan the nonclustered index and count up qualifying rows. Since the cluster is the table, it's usually the biggest index.

    This is especially likely if the range that you've specified qualifies most or all of the rows in the table.

    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

  • January 21, 2010 at 11:04 am

    #1106850

    Thank you for responding.

    I changed the date range(wider) and it used cluster index.

    Thank you again.

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

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