Dela via

  • Artikel

Breaking 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 breaking changes in the Database Engine. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. You might encounter these issues when you upgrade. For more information, see Using Upgrade Advisor to Prepare for Upgrades.

Collations

Feature

Description

New collations

SQL Server 2008 introduces new collations that are in full alignment with the collations provided by Windows Server 2008. These 80 new collations have improved linguistic accuracy and are denoted by *_100 version references. If you choose a new collation for your server or database, be aware that the collation may not be recognized by clients with older client drivers. Unrecognized collations can cause the application to return errors and fail. Consider the following solutions:

  • Upgrade the client operating system so that the underlying system collations are updated.

  • If your client has database client software installed, consider applying a service update to the database client software.

  • Choose an existing collation that maps to a code page on the client.

For more information, see Setting and Changing Collations.

Common Language Runtime (CLR)

Feature

Description

CLR Assemblies

When a database is upgraded to SQL Server 2008, the Microsoft.SqlServer.Types assembly to support new data types is automatically installed. Upgrade Advisor rules detect any user type or assemblies with conflicting names. The Upgrade Advisor will advise renaming of any conflicting assembly, and either renaming any conflicting type, or using two-part names in the code to refer to that preexisting user type.

If a database upgrade detects a user assembly with a conflicting name, it will automatically rename that assembly and put the database into suspect mode.

If a user type with conflicting name exists during the upgrade, no special steps are taken. After the upgrade, both the old user type, and the new system type, will exist. The user type will be available only through two-part names.

CLR Assemblies

SQL Server 2008 installs .NET Framework 3.5 SP1, which updates libraries in the Global Assembly Cache (GAC). If you have unsupported libraries registered in a SQL Server database, your SQL Server application may stop working after upgrading to SQL Server 2008. This is because servicing or upgrading libraries in the GAC does not update assemblies inside SQL Server. If an assembly exists both in a SQL Server database and in the GAC, the two copies of the assembly must exactly match. If they do not match, an error will occur when the assembly is used by SQL Server CLR integration. For more information, see Supported .NET Framework Libraries.

After upgrading your database, service or upgrade the copy of the assembly inside your SQL Server databases with the ALTER ASSEMBLY statement. For more information, see Knowledge Base article 949080.

To detect whether you are using any unsupported .NET framework library in your application, run the following query in your database.

SELECT name FROM sys.assemblies WHERE clr_name LIKE '%publickeytoken=b03f5f7f11d50a3a,%';

CLR Routines

Using impersonation inside CLR user-defined functions, user-defined aggregates, or user-defined types (UDTs) may cause your application to fail with error 6522 after upgrading to SQL Server 2008.

The following scenarios succeed in SQL Server 2005 but fail in SQL Server 2008. Resolutions are provided for each scenario.

  1. A CLR user-defined function, user-defined aggregate, or UDT method that uses impersonation has a parameter of type nvarchar(max), varchar(max), varbinary(max), ntext, text, image, or a large UDT, and does not have the DataAccessKind.Read attribute on the method.

    To resolve this issue, add the DataAccessKind.Read attribute on the method, recompile the assembly, and re-deploy the routine and the assembly.

  2. A CLR table-valued function that has an Init method that performs impersonation.

    To resolve this issue add the DataAccessKind.Read attribute on the method, recompile the assembly, and re-deploy the routine and the assembly.

  3. A CLR table-valued function that has a FillRow method that performs impersonation.

    To resolve this issue, remove impersonation from the FillRow method. Do not access external resources by using the FillRow method. Instead, access external resources from the Init method.

Dynamic Management Views

View

Description

sys.dm_os_sys_info

Removed the cpu_ticks_in_ms and sqlserver_start_time_cpu_ticks columns.

sys.dm_exec_query_resource_semaphores sys.dm_exec_query_memory_grants

The resource_semaphore_id column is not a unique ID in SQL Server 2008. This change can affect troubleshooting query execution. For more information, see sys.dm_exec_query_resource_semaphores (Transact-SQL).

Errors and Events

Feature

Description

Login errors

In SQL Server 2005, error 18452 is returned when a SQL login is used to connect to a server configured to use only Windows Authentication. In SQL Server 2008, error 18456 is returned instead.

Showplan

Feature

Description

Showplan XML schema

A new SeekPredicateNew element is added to the Showplan XML schema, and the enclosing xsd sequence (SqlPredicatesType) is converted into an <xsd:choice> item. Instead of one or more SeekPredicate elements, one or more SeekPredicateNew elements may now appear in the Showplan XML. The two elements are mutually exclusive. SeekPredicate is maintained in the Showplan XML schema for backwards compatibility; however, query plans created in SQL Server 2008 may contain the SeekPredicateNew element. Applications that expect to retrieve only the SeekPredicate child from the node ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/IndexScan/SeekPredicates may fail if the SeekPredicate element does not exist. Rewrite the application to expect either the SeekPredicate or SeekPredicateNew element in this node. For more information, see Query Processing Enhancements on Partitioned Tables and Indexes.

Showplan XML schema

A new IndexKind attribute is added to the ObjectType complex type in the Showplan XML schema. Applications that strictly validate SQL Server plans against the SQL Server 2005 schema will fail.

Transact-SQL

Feature

Description

ALTER_AUTHORIZATION_DATABASE DDL event

In SQL Server 2005, when the DDL event ALTER_AUTHORIZATION_DATABASE fires, the value 'object' is returned in the ObjectType element of the EVENTDATA xml for this event when the entity type of the securable in the data definition language (DDL) operation is an object. In SQL Server 2008, the actual type (for example, 'table', or 'function') is returned.

CONVERT

If an invalid style is passed to the CONVERT function, an error is returned when the type of conversion is binary to character or character to binary. In earlier versions of SQL Server, the invalid style is set to the default style for binary-to-character and character-to-binary conversions.

GRANT/DENY/REVOKE EXECUTE on assemblies

EXECUTE permission cannot be granted, denied, or revoked to assemblies. This permission has no affect and now causes an error. Grant, deny, or revoke EXECUTE permission on the stored procedures or functions that reference the assembly method instead.

GRANT/DENY/REVOKE permissions on system types

Permissions cannot be granted, denied, or revoked to system types. In earlier versions of SQL Server, these statements succeed but have no effect. In SQL Server 2008, an error is returned.

GROUP BY

The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.

For example, the following code will succeed in SQL Server 2005 and fail in SQL Server 2008.

DECLARE @Test TABLE(a int NOT NULL);
INSERT INTO @Test SELECT 1 union ALL SELECT 2;
SELECT COUNT(*) 
FROM @Test
GROUP BY CASE WHEN a IN (SELECT t.a FROM @Test AS t)
THEN 1 ELSE 0 
END;

OUTPUT clause

To prevent nondeterministic behavior, the OUTPUT clause cannot reference a column from a view or inline table-valued function when that column is defined by one of the following methods:

  • A subquery.

  • A user-defined function that performs user- or system-data access, or is assumed to perform such access.

  • A computed column that contains in its definition a user-defined function that performs user- or system-data access.

When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.

OUTPUT INTO clause

The target table of the OUTPUT INTO clause cannot have any enabled triggers.

precompute rank server-level option

This option is not supported in SQL Server 2008. Modify applications that currently use this feature as soon as possible.

READPAST table hint

You cannot specify the READPAST hint under Snapshot Isolation.

The READPAST hint is ignored when either the READ_COMMITED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set to ON. However, if you combine the READPAST hint with READCOMMITTEDLOCK, the READPAST behavior is same as with the blocking READCOMMITTED hint.

sp_helpuser

The following column names that are returned in the result set of the sp_helpuser stored procedure have changed.

Previous column nameNew column name
GroupNameRoleName
Group_nameRole_name
Group_idRole_id
Users_in_groupUsers_in_role

Transparent Data Encryption

Transparent data encryption (TDE) is performed at the I/O level: the page structure is unencrypted in memory and is encrypted only when the page is written to disk. Both the database files and log files are encrypted. Third-party applications that bypass the regular SQL Server mechanism for accessing pages (for example, by scanning the data or log files directly), will fail when a database uses TDE because the data is encrypted in the files. Such applications can leverage the Window Cryptographic API to develop a solution for decrypting the data outside of SQL Server.

XQuery

Feature

Description

Datetime support

In SQL Server 2005, the data types xs:time, xs:date, and xs:dateTime do not have timezone support. Timezone data is mapped to the UTC timezone. SQL Server 2008, provides standard conformant behavior, resulting in the following changes:

  • Values without timezone are validated.

  • The provided timezone or the absence of a timezone is preserved.

  • The internal storage representation is modified.

  • Resolution of stored values is increased.

  • Negative years are disallowed.

Modify applications and XQuery expressions to account for the new type values. For more information, see Using XML with time, date, datetime2, and datetimeoffset Data Types.

XQuery and Xpath expressions

In SQL Server 2005, steps in an XQuery or XPath expression that begin with a colon (':') are allowed. For example, the following statement contains a name test (CTR02) within the path expression that begins with a colon.

SELECT FileContext.query('for n$ in //CTR return <C>{data )(n$/:CTR02)} </C>) AS Files FROM dbo.MyTable;

In SQL Server 2008, this usage is disallowed because it does not conform to XML standards. Error 9341 is returned. Remove the leading colon or specify a prefix for the name test--for example, (n$/CTR02) or (n$/p1:CTR02).

Connecting

Feature

Description

Connecting from SQL Server Native Client using SSL

When connecting with SQL Server Native Client, applications that use "SERVER=shortname; FORCE ENCRYPTION=true" with certificate whose Subjects specify Fully Qualified Domain Names (FQDN's) have connected in the past due to relaxed validation. SQL Server 2008 R2 enhances security by enforcing FQDN subjects for certificates. Applications that rely upon relaxed validation must take one of the following actions:

  • Use the FQDN in the connection string.

    • This option does not require recompiling the application if the SERVER keyword of the connection string is configured outside the application.

    • This option does not work for applications that have their connection strings hardcoded.

    • This option does not work for applications that use Database Mirroring since the mirrored server replies with a simple name.

  • Add an alias for the shortname to map to the FQDN.

    • This option works even for applications that have their connection strings hardcoded.

    • This option does not work for applications that use Database Mirroring since the providers don’t look up aliases for received failover partner names.

  • Have a certificate issued for shortname.

    • This option works for all applications.