• idyana (12/26/2012)


    How do I want to make both of them (State and City) is Index Seek?

    You can't.

    A seek requires something to search for. Something to filter on. Your query requests the entire of two tables. The only sensible way for the entire of two tables to be queried is with a table scan.

    This is true no matter how large the tables are (in fact in this case, larger tables would almost certainly result in two table scans because a table scan is the optimal way to read the entire of a table).

    There is no possible way for your query to use two index seeks. One index seek comes from the join, but there's nothing seekable for the other. If you added a where clause to filter for specific cities or states you might see index seeks for both, because there would be something to search for.

    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