Sergiy (2/3/2013)
Different story when you apply the WHERE clause.Because there is no way any index can be used SQL Server needs to build the recordset from the subquery as a table in memory, and then apply the filter against it.
Not how filters work. Sure, that's not something where SQL can seek for a value, but it can read from the index/table and apply the filter as it goes. Secondary filters are not a blocking operation, that's sorts, hash joins, hash aggregates, things where the entire resultset has to be available to work on.
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