Post reply

Not showing Index in the Execution Plan

  • September 27, 2002 at 1:21 am

    #78875

    Hello All

    I have created the a table which is having a primary key c1 and one field c2. As default SQL server will create clusertered index on c1. Now i have created a non-clustered index on the table on col c2.

    My query will always be

    select * from t1 where c2 = ''

    But when i see the execution plan , i am not getting the non-clusetered index scan, but it is showing clusetered index scan on the primary key field of the table.

    Can any1 explain me y?

  • September 27, 2002 at 1:45 am

    #437369

    This is probably caused by the optimizer deciding that a clustered index scan checking each rows value in c2 will be as quick as first going to the non-clustered index and fetching all rows with correct value in c2, then going back to the clustered index to fetch the other columns for the output. How many rows do you have in the table? What kind of values do you have in c2? How distinct are these values from eachother?

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • September 27, 2002 at 4:56 am

    #437376

    Yes sir u r right, but theoretically speaking the query optimizer should scan with the nonclustered index bcoz its in the where clause.

    There r somewhere around 40000 records in that table. Both of them is a uniqueidentifier..

    Also when i try to give the with index option, its showing quesry plan with non-clustered index, but the I/O cost and CPU Cost is much higher then the clustered index scan.

    I have some ard 100 records for each value of c2 in the table and corresponding newid() c1 in the table.

    thnks for the reply.

    quote:

    This is probably caused by the optimizer deciding that a clustered index scan checking each rows value in c2 will be as quick as first going to the non-clustered index and fetching all rows with correct value in c2, then going back to the clustered index to fetch the other columns for the output. How many rows do you have in the table? What kind of values do you have in c2? How distinct are these values from eachother?

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

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

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