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 Assemblies

SQL Server 2008 installs .NET Framework 3.5 SP1, which updates libraries in the Global Access 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.

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

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

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 returned by against the SQL Server 2005 schema will fail.

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.

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.

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.

sp_helpuser

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

Previous column name	New column name
GroupName	RoleName
Group_name	Role_name
Group_id	Role_id
Users_in_group	Users_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.

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.