SQL Server Performance

Why the Query Engine does not use indexes when using the IN operator?

Discussion in 'SQL Server 2008 General DBA Questions' started by EMoscosoCam, Feb 19, 2009.

  1. EMoscosoCam Member

    Hello
    I hae read that when using the IN operator, the query engine does not use indexes. So my question is, shouldn't the query engine be smart enough to convert the enumeration in a structure "compatible" with indexes?
    Thanks a lot.
    EMoscosoCam, Feb 19, 2009
    #2

  2. FrankKalis Moderator

    Hm, I'm not using IN() at all, but a quick test shows me that an index can be used. I would imagine though, that it greatly depends on how many elements are contained in the IN clause.
    As IN() is a shortcut to multiple ORs it might be cheaper to ignore the index and go for a scan right-away, when the number if elements is too high.
    FrankKalis, Feb 19, 2009
    #3

  3. Adriaan New Member

    If you need to filter on a column for a series of values, and the column has an FK, then check if this type of filtering can be "translated" as an additional column on the table that holds the RK, which allows you to group key values.
    Now your IN clause can be replaced by a subquery that filters on that additional column on the RK table.
    Adriaan, Feb 20, 2009
    #4

  4. FrankKalis Moderator

    What do you mean with "RK" please?
    FrankKalis, Feb 20, 2009
    #5

  5. Adriaan New Member

    Frank - remember the days when we were still at the cutting edge with SQL 2000, and there was the sysforeignkeys system table with the fkey and rkey columns?
    BOL talks of fkey as "the referencing column" and of rkey as "the referenced column", so from FK as short for Foreign Key you would have RK for Referenced Key.
    Now you're telling me that it's not an official concept?![:S][H]
    Adriaan, Feb 20, 2009
    #6

  6. FrankKalis Moderator

    Don't worry. I won't ruin your weekend. [:)]
    I guessed so, but it just looked "strange".
    FrankKalis, Feb 20, 2009
    #7

  7. Adriaan New Member

    Na, it's already ruined with the atrocities of carnival.[li]
    Adriaan, Feb 20, 2009
    #8

Share This Page