SQL Server 2008 changes the way queries on partitioned tables and indexes are processed. Queries on partitioned objects that use the USE PLAN hint for a plan generated by SQL Server 2005 might contain an invalid plan. For more information, see Considerations for Upgrading the Database Engine. For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.

SQL Server 2008 changes the transaction semantics of INSERT...EXECUTE statements that execute against a loopback linked server. In SQL Server 2005, this scenario is not supported and causes an error. In SQL Server 2008, an INSERT...EXECUTE statement can execute against a loopback linked server when the connection does not have multiple active result sets (MARS) enabled. When MARS is enabled on the connection, the behavior is the same as in SQL Server 2005.

In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation of SQL Server, the default value remains NONE. The option can be modified. We recommend that you use CHECKSUM for the tempdb database.

In SQL Server 2008, if a plan guide cannot be honored, the query compiles using a different plan and no error is returned. In SQL Server 2005, an error is raised and the query fails.

Plan guides created in SQL Server 2005 may not be valid after upgrading to SQL Server 2008. Invalid plan guides will not cause the application to fail, but the plan guide will not be used. We recommend re-evaluating and testing plan guide definitions when you upgrade your application to a new release of SQL Server. Performance tuning requirements and plan guide matching behavior may change. Although an invalid plan guide will not cause a query to fail, the plan is compiled without using the plan guide. After you upgrade a database to SQL Server 2008, you should perform the following tasks to validate existing plan guides by using the sys.fn_validate_plan_guide function. Alternatively, you can monitor for invalid plan guides by using the Plan Guide Unsuccessful event in SQL Server Profiler.

In SQL Server 2005, trailing spaces specified in the input parameters to the REPLACE function are trimmed. In SQL Server 2008, trailing spaces are preserved. For applications that rely on the previous behavior of the function, use the RTRIM function when specifying the input parameters for the function. For example, the following syntax will trim trailing spaces from each input parameter in the REPLACE function: REPLACE(RTRIM(input_parameter), RTRIM(input_parameter), RTRIM(input_parameter)).

Reference

Other Resources