Post reply

Index Usage

  • July 30, 2008 at 5:07 am

    #196253

    Hi,

    Just trying to better unstand the index usage.

    Scenario,

    I have a table;

    CREATE TABLE dbo.SampleTableA

    (

    ColumnA INT,

    ColumnB VARCHAR(255)

    )

    then i create a clustered index on the table

    CREATE CLUSTERED INDEX CIX_SampleTableIndex ON SampleTableA (ColumnA)

    When i run a select statment;

    SELECT * FROM SampleTableA WHERE ColumnA = 1

    it uses the clustered index in the execution, however when i execute the second select statement

    SELECT * FROM SampleTableA WHERE ColumnB = 'a'

    it uses the same plan, and the again uses the clustered index scan.

    I am a bit confused, as 'ColumnB' is not part of the clustered index how come it is using it in the plan for the second select?

    Thanks,

    Nic

  • July 30, 2008 at 6:33 am

    #850419

    Here is my take on it. (90% confident)

    Because it is a clustered index, the leaf level pages of the index is the data rows in the table. So you will always see an index scan for the table even though it is doing a table scan.

    SQL server will report an index scan on the table when it does a table scan because when it moves through the table scan it is actually scanning the index pages as well - they are the same.

  • July 30, 2008 at 6:40 am

    #850426

    A clustered index scan is a table scan. It's a full and complete read of all of the data pages in the table. You see the operation as a table scan if the table is a heap (has no clustered index) or as a clustered index scan if there is a clustered index.

    It's using the clustered index because there is no index on ColumnB, hence no other way to find the rows other than to scan the entire 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

  • July 30, 2008 at 6:44 am

    #850428

    Thanks both,

    That makes perfect sense.

    Nic

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

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