• rd8202 (3/1/2013)


    Instead of the CTE, can't you just put the CTE's SELECT statement inside the WHERE clause?

    You can do it that way; it's really just a syntactical change. There's a slight difference in the query plan (as the article shows -- it does cover both approaches), but SQL Server ends up doing much the same work. You're exchanging a filter (the IN clause) for a join (the CTE), and both are pretty optimal operations.

    Where there's a difference is where you decide to use a temporary table. I'll do that for two reasons: either I'm expecting more than an handful of values or I'm reusing the list of values in subsequent queries. For the first, I can create a temporary table with a primary key so SQL Server can do an index merge for the join, and that seems to work pretty well.