Several problems relating to subqueries have been fixed in SQL Server release 11.0. This section describes old and new behavior in detail. Examine applications that use subqueries to determine if your application relies on behavior that has been corrected.
Subquery processing in previous releases of SQL Server used “inside-out” processing. Release 11.0 subqueries are processed “outside-in” for greater efficiency. Upgrading to release 11.0 does not automatically change the processing mode of the subquery.
After upgrading to release 11.0, you must drop and re-create objects to take advantage of the new processing style. Use sp_procqmode to identify objects that include subqueries. After upgrading to SQL Server release 11.0, you cannot create an object that uses release 10.0 processing.
It is best to test procedures that contain subqueries and make any changes that are necessary for your application before upgrading your production system.
