Breaking Changes to Database Engine Features in SQL Server 2008

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.




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)



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




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



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.

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.





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.


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.


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.

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.


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

The READPAST table hint cannot be specified when the READ_COMMITTED_SNAPSHOT database option is set to ON and either of the following conditions are true:

  • The transaction isolation level of the session is READ COMMITTED.

  • The READCOMMITTED table hint is also specified in the query.

To specify the READPAST hint in these cases, remove the READCOMMITTED table hint if present, and include the READCOMMITTEDLOCK table hint in the query.


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

Previous column nameNew column name

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.

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

Change History

Updated content

Added breaking change for XQuery and Xpath expressions.