Post reply

Will index sequence help in sequence of WHERE caluse?

  • September 5, 2008 at 5:25 am

    #190109

    I'm not sure if WHERE clause should match exactly with index sequence to return the data quicker. For example I have created non-clustered index on Employee table for FirstName and LastName columns. From following which query will return the data faster?

    Query#1

    select * from Employee where FirstName = 'MyFirstName' and LastName = 'MyLastName'

    Query#2

    select * from Employee where LastName = 'MyLastName' and FirstName = 'MyFirstName'

    Thanks - JL

    Regards - JL

  • September 5, 2008 at 5:37 am

    #867672

    If you have create an index with FirstName and LastName, both sentences will be the same.

  • September 5, 2008 at 6:00 am

    #867685

    It doesn't make any difference in which order you write the WHERE clause.

    SQL Server will look at the WHERE clause as a whole, and work out the best way to access the table.

  • September 5, 2008 at 7:13 am

    #867733

    So does it mean, the index sequence can be decided on any order? Or is there any logic to decide the index ordering?

    Will Index (FirstName, LastName) be exactly same as Index (LastName, FirstName)?

    Regards - JL

  • September 5, 2008 at 8:38 am

    #867818

    No they won't. You need to pick out the order so that the first column is the most granular... or makes the most sens for order bys.

    In the case of LastName and FirstName, I usually put them in that order because I always present the data in that order... so since the index is sorted in the order, the server will not have to resort the data when fetching it.

  • September 5, 2008 at 10:25 am

    #867900

    jluniya (9/5/2008)

    So does it mean, the index sequence can be decided on any order? Or is there any logic to decide the index ordering?

    There is, and it depends on the queries that you have. If you put an index on (Lastname, firstname) then queries with the following 2 where clauses can seek on that index

    WHERE FirstName = @Param1 and LastName = @Param2

    WHERE LastName = @Param2

    A where clause of the following form will have to scan.

    WHERE FirstName = @Param1

    Indexes are useful (seekable) for queries whose where clause predicates are a left-base subset of the index keys.

    Make sense?

    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

Viewing 6 posts - 1 through 5 (of 5 total)

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