Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003

    Unanswered: How do I make it use my index?


    I have two tables

    users and pictures.

    table users have a clustered (PK) index on userid
    table pictures have a clustered (PK) index on userid

    when I do this query:

    "select userid from pictures where userid=123"

    then It will do a clustered index seek

    But If I do any of those:

    "select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
    "select (select userid from pictures where usedid = t1.userid) from users t1"

    It will do a clustered index scan.

    How can I force it to seek my index instead of scan?


  2. #2
    Join Date
    Dec 2004
    do you have some where clause at the end of the query? when not, then the plan created by sql server is perhaps really the best!
    if the where clause shrinks the number of rows in t1 to a fraction of total table row count, you could use

    option(loop join)

    at the end of the query. this should solve your problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts