• Article

Behavior Changes to Database Engine Features in SQL Server 2008 R2

Note

For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic.

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.

Linked Servers

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.

Plan Guides

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.

Partitioned Table Queries That Use the USE PLAN Hint

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:

  1. Remove the USE PLAN hint from the query.

  2. Test the query.

  3. If the optimizer does not select an appropriate plan, tune the query and then specify the USE PLAN hint with the desired query plan.

When the USE PLAN hint is specified in a plan guide:

  1. Use the sys.fn_validate_plan_guide function to check the validity of the plan guide. Alternatively, you can check for invalid plans by using the Plan Guide Unsuccessful event in SQL Server Profiler.

  2. If the plan guide is invalid, drop the plan guide. If the optimizer does not select an appropriate plan, tune the query and then specify the USE PLAN hint with the desired query plan.

For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.

Query Processor Architecture

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.

REPLACE Function

In SQL Server 2005, trailing spaces specified in the first input parameter to the REPLACE function are trimmed when the parameter is of type char. For example, in the statement SELECT '<' + REPLACE(CONVERT(char(6), 'ABC '), ' ', 'L') + '>', the value 'ABC ' is incorrectly evaluated as 'ABC'.

In SQL Server 2008, trailing spaces are always preserved. For applications that rely on the previous behavior of the function, use the RTRIM function when specifying the first input parameter for the function. For example, the following syntax will reproduce the SQL Server 2005 behavior SELECT '<' + REPLACE(RTRIM(CONVERT(char(6), 'ABC ')), ' ', 'L') + '>'.

Resource Database

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_50.<instance_name>\Binn\. The Resource database cannot be moved.

tempdb Database

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.

Changes to Behavior in Scripting a SQL Server Agent Task

In For SQL Server 2008 R2, there have been no changes to the content that is listed in this topic., if you create a new job by copying the script from an existing job, the new job might inadvertently affect the existing job. To create a new job using the script from an existing job, manually delete the parameter @schedule_uid which is usually the last parameter of the section which creates the job schedule in the existing job. This will create a new independent schedule for the new job without affecting existing jobs.

Changes to Behavior in sp_tables

In SQL Server 2000, calling sp_tables returns the list of only user objects. Beginning with SQL Server 2005, this call returns the list of all accessible user objects; user views as well as the system views and catalog views that belong to the SYS and INFORMATION_SCHEMA schemas.