Breaking Changes to Database Engine Features in SQL Server 2008 R2

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.

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 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.

sys.dm_os_sys_info

Removed the cpu_ticks_in_ms and sqlserver_start_time_cpu_ticks columns.

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 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.

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.

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.

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 INTO clause

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

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.

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.

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.

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.