This topic describes behavior changes in the Database Engine. Behavior changes affect how features work or interact in SQL Server 2008 as compared to earlier versions of SQL Server.
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 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. 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.
SQL Server 2008 changes the way queries on partitioned tables and indexes are processed. Queries on partitioned objects that use the USE PLAN hint might contain an invalid plan. We recommend the following procedures after upgrading to SQL Server 2008.
When the USE PLAN hint is specified directly in a query:
When the USE PLAN hint is specified in a plan guide:
For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.
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.
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)).
REPLACE(RTRIM(
), RTRIM(
))
In SQL Server 2005, the data and log files for the Resource database depend on the location of the data file of the master database. Therefore, moving the master database also requires moving the Resource database to the same location. In SQL Server 2008, this dependency does not exist. The master database files can be moved without moving the Resource database.
In SQL Server 2008, the default location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\Binn\. The Resource database can be moved; however, we recommend not moving it for two reasons:
For more information, see Moving System Databases.
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.