Subqueries in objects created in SQL Server release 11.0 may return different results than subqueries in objects created on an earlier SQL Server release. To test performance after upgrade, create the objects in SQL Server release 11.0, test the results and performance, and make any changes that are necessary for your application.
A new system procedure, sp_procqmode, reports on the subquery processing mode of an object.
As of release 10.0, subqueries using in or any predicates no longer return duplicates.
set dup_in_subquery on was provided as an upgrade path to SQL Server release 10.0. It is no longer supported. If your application depends on the duplicate rows in the result set, rewrite the subquery as a join.
For example:
select a from s where b in
(select c from t)
would become:
select a from s, b where s.b = t.c
Procedures created in SQL Server release 10.0 that use dup_in_subquery will continue to run until the procedure is dropped and re-created in release 11.0.
