Post reply

Indexes

  • October 26, 2010 at 11:25 pm

    #230373

    In Adventure works DB if the following query is run with Include Actual Execution Plan on

    SELECT COUNT(*) FROM Person.Address

    the plan shows that it uses a non-clustered index named 'IX_Address_StateProvinceID'

    My confusion is on what basis does sql chose that index?

    Why Nonclustered and not Clustered?

    Thanks in advance

  • October 26, 2010 at 11:40 pm

    #1242663

    That's the smallest index on that table and hence the lowest IOs involved in a scan of every single index page. The cluster is likely to be the largest index on the table, so many, many more IOs (and memory displacement) to get exactly the same thing.

    For a count, SQL doesn't need any particular column value, it just needs to know that the row is there, hence is will pick a nonclustered index (which has the same row count as the table) and just scan the leaf level counting up rows.

    Now if that was count(<column name>) or there was a where clause things would be a lot more difficult

    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

  • October 26, 2010 at 11:52 pm

    #1242669

    Thank you very much for the reply.

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

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