Share via

Breaking Changes to Database Engine Features in SQL Server 2012

  • Article

This topic describes breaking changes in the SQL Server 2012 Database Engine and earlier versions of SQL Server. 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 Use Upgrade Advisor to Prepare for Upgrades.

In This Topic

Breaking Changes in SQL Server 2012

Breaking Changes in SQL Server 2008/SQL Server 2008R2

Breaking Changes in SQL Server 2005

Breaking Changes in SQL Server 2012

Transact-SQL

Feature

Description

Selecting from columns or tables named NEXT

Sequences use the ANSI standard NEXT VALUE FOR function. If a table or a column is named NEXT and the table or column is aliased as VALUE, and if the ANSI standard AS is omitted, the resultant statement can cause an error. To work around, include the ANSI standard AS keyword. For example, SELECT NEXT VALUE FROM Table should be rewritten as SELECT NEXT AS VALUE FROM Table and SELECT Col1 FROM NEXT VALUE should be rewritten as SELECT Col1 FROM NEXT AS VALUE.

PIVOT operator

The PIVOT operator is not allowed in a recursive common table expression (CTE) query when the database compatibility level is set to 110. Rewrite the query, or change the compatibility level to 100 or lower. Using PIVOT in a recursive CTE query produces incorrect results when there is more than a single row per grouping.

sp_setapprole and sp_unsetapprole

The cookie OUTPUT parameter for sp_setapprole is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(50). Applications should continue to reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release. For more information, see sp_setapprole (Transact-SQL).

EXECUTE AS

The cookie OUTPUT parameter for EXECUTE AS is currently documented as varbinary(8000) which is the correct maximum length. However the current implementation returns varbinary(100). Applications should continue to reserve varbinary(8000) so that the application continues to operate correctly if the cookie return size increases in a future release. For more information, see EXECUTE AS (Transact-SQL).

sys.fn_get_audit_file function

Two additional columns (user_defined_event_id and user_defined_information) have been added to support user-defined audit events. Applications that do not select columns by name might return more columns than expected. Either select columns by name, or adjust the application to accept these additional columns.

CAST and CONVERT operations on computed columns of type time or datetime2

In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121 except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

Under compatibility level 110, the default style for CAST and CONVERT operations on time and datetime2 data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

Upgrading the database to compatibility level 110 will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source that contained a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You would need to manually update this data to match style 121.

ALTER TABLE

The ALTER TABLE statement allows only two-part (schema.object) table names. Specifying a table name using the following formats now fails at compile time with error 117.

  • server.database.schema.table

  • .database.schema.table

  • ..schema.table

In earlier versions specifying the format server.database.schema.table returned error 4902. Specifying the format .database.schema.table or the format ..schema.table succeeded.

To resolve the problem, remove the use of a 4-part prefix.

Browsing metadata

Querying a view using FOR BROWSE or SET NO_BROWSETABLE ON now returns the metadata of the view, not the metadata of the underlying object. This behavior now matches other methods of browsing metadata.

SOUNDEX

Under database compatibility level 110, the SOUNDEX function implements new rules that may cause the values computed by the function to be different than the values computed under earlier compatibility levels. After upgrading to compatibility level 110, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function. For more information, see SOUNDEX (Transact-SQL).

Row count message for failed DML statements

In SQL Server 2012, the Database Engine will consistently send the TDS DONE token with RowCount: 0 to clients when a DML statement fails. In earlier versions of SQL Server, an incorrect value of -1 is sent to the client when the DML statement that fails is contained in a TRY-CATCH block and is either autoparameterized by the Database Engine or the TRY-CATCH block is not on the same level as the failed statement. For example, if a TRY-CATCH block calls a stored procedure and a DML statement in the procedure fails, the client will incorrectly receive a -1 value.

Applications that rely on this incorrect behavior will fail.

SERVERPROPERTY (‘Edition’)

Installed product edition of the instance of SQL Server 2012. Use the value of this property to determine the features and the limits, such as maximum number of CPUs that are supported by the installed product.

Based on the installed Enterprise edition, this can return ‘Enterprise Edition’ or ‘Enterprise Edition: Core-based Licensing’. The Enterprise editions are differentiated based on the maximum compute capacity by a single instance of SQL Server. For more information on compute capacity limits in SQL Server 2012, see Compute Capacity Limits by Edition of SQL Server.

CREATE LOGIN

The CREATE LOGIN WITH PASSWORD = 'password' HASHED option cannot be used with hashes created by SQL Server 7 or earlier.

CAST and CONVERT operations for datetimeoffset

The only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809. For example, the following code returns error 9809.

SELECT CONVERT(date, CAST('7070-11-25 16:25:01.00986 -02:07' as datetimeoffset(5)), 107);

SET ROWCOUNT

The behavior of SET ROWCOUNT was changed to improve the efficiency of many common queries and can affect the query plans for compatibility levels 110 and higher. An error can result when the SET ROWCOUNT statement changes the ROWCOUNT value to 0 or from 0 after a DECLARE CURSOR statement and before a FETCH statement. To avoid this error, set the database compatibility level to 100 or set the ROWCOUNT outside of the cursor statements.

Dynamic Management Views

View

Description

sys.dm_exec_requests

The command column changes from nvarchar(16) to nvarchar(32).

sys.dm_os_memory_cache_counters

The following columns have been renamed.

Previous Column Name

New Column Name

single_pages_kb

pages_kb

multi_pages_kb

pages_in_use_kb

sys.dm_os_memory_cache_entries

The column pages_allocated_count column has been renamed pages_kb.

sys.dm_os_memory_clerks

The column multi_pages_kb has been removed.

The column single_pages_kb column has been renamed pages_kb.

sys.dm_os_memory_nodes

The following columns have been renamed.

Previous Column Name

New Column Name

single_pages_kb

pages_kb

multi_pages_kb

foreign_committed_kb

sys.dm_os_memory_objects

The following columns have been renamed.

Previous Column Name

New Column Name

pages_allocated_count

pages_in_bytes

max_pages_allocated_count

max_pages_in_bytes

sys.dm_os_sys_info

The following columns have been renamed.

Previous Column Name

New Column Name

physical_memory_in_bytes

physical_memory_kb

bpool_commit_target

committed_target_kb

bpool_visible

visible_target_kb

virtual_memory_in_bytes

virtual_memory_kb

bpool_commited

committed_kb

sys.dm_os_workers

The locale column has been removed.

Catalog Views

View

Description

sys.data_spaces

sys.partition_schemes

sys.filegroups

sys.partition_functions

A new column, is_system, has been added to sys.data_spaces and sys.partition_functions. (sys.partition_schemes and sys.filegroups inherit the columns of sys.data_spaces.)

A value of 1 in this column indicates that the object is used for full-text index fragments.

In sys.partition_functions, sys.partition_schemes, and sys.filegroups, the new column is not the last column. Revise existing queries that rely on the order of columns returned from these catalog views.

SQL CLR Data Types (geometry, geography, and hierarchyid)

The assembly Microsoft.SqlServer.Types.dll, which contains the spatial data types and the hierarchyid type, has been upgraded from version 10.0 to version 11.0. Custom applications that reference this assembly may fail when the following conditions are true.

  • When you move a custom application from a computer on which SQL Server 2008 R2 was installed to a computer on which only SQL Server 2012 is installed, the application will fail because the referenced version 10.0 of the SqlTypes assembly is not present. You may see this error message: “Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.”

  • When you reference the SqlTypes assembly version 11.0, and version 10.0 is also installed, you may see this error message: “System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeometry' to type 'Microsoft.SqlServer.Types.SqlGeometry'.”

  • When you reference the SqlTypes assembly version 11.0 from a custom application that targets .NET 3.5, 4, or 4.5, the application will fail because SqlClient by design loads version 10.0 of the assembly. This failure occurs when the application calls one of the following methods:

    • GetValue method of the SqlDataReader class

    • GetValues method of the SqlDataReader class

    • bracket index operator [] of the SqlDataReader class

    • ExecuteScalar method of the SqlCommand class

You can work around this issue by using one of the following methods:

  • You can work around this issue in your code by calling the GetSqlBytes method, instead of the Get methods listed above, to retrieve CLR SQL Server system types, as shown in the following example:

    string query = "SELECT [SpatialColumn] FROM [SpatialTable]";
      using (SqlConnection conn = new SqlConnection("..."))
      {
            SqlCommand cmd = new SqlCommand(query, conn);

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                  // In version 11.0 only
                  SqlGeometry g = 
SqlGeometry.Deserialize(reader.GetSqlBytes(0));

                  // In version 10.0 or 11.0
                  SqlGeometry g2 = new SqlGeometry();
                  g.Read(new BinaryReader(reader.GetSqlBytes(0).Stream));
            }
      }

  • You can work around this issue by using assembly redirection in the application configuration file, as shown in the following example:

    <runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    ...
    <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0" />
    </dependentAssembly>
    ...
</assemblyBinding>
<runtime>

  • You can work around this issue in your connection string by specifying a value of "SQL Server 2012" for the "Type System Version" attribute to force SqlClient to load version 11.0 of the assembly. This connection string attribute is available only in .NET 4.5 and above.

  • The assemblyBinding tag should be wrapped under the runtime tag.

Support for AWE

32-bit Address Windowing Extensions (AWE) support is discontinued. This might result in slower performance on 32-bit operating systems. For installations using large amounts of memory, migrate to a 64-bit operating system.

XQuery Functions Are Surrogate-Aware

The W3C recommendation for XQuery functions and operators requires them to count a surrogate pair that represents a high-range Unicode character as a single glyph in UTF-16 encoding. However, in versions of SQL Server prior to SQL Server 2012, string functions did not recognize surrogate pairs as a single character. Some string operations – such as string length calculations and substring extractions – returned incorrect results. SQL Server 2012 now fully supports UTF-16 and the correct handling of surrogate pairs.

The XML data type in SQL Server only allows well-formed surrogate pairs. However, some functions can still return undefined or unexpected results in certain circumstances, since it is possible to pass invalid or partial surrogate pairs to XQuery functions as string values. Consider the following methods for generating string values when using XQuery in SQL Server:

  • Provide a constant string value as a binary value. When using this method, it remains possible to pass invalid or partial surrogate pairs.

  • Provide a constant string value by providing character entities. When using this method, it is not possible to pass invalid surrogate pairs. The XQuery functions require a single character entity for the high-level character. These functions raise an error if the character entities for the surrogate pair characters are provided.

  • Import external values by using sql:column or sql:variable. When using these methods, it remains possible to introduce invalid or partial surrogate pairs.

Affected XQuery Functions and Operators

The following XQuery functions and operators now handle UTF-16 surrogate pairs correctly in SQL Server 2012:

  • fn:string-length. However, if an invalid or partial surrogate pair is passed as an argument, the behavior of string-length is undefined.

  • fn:substring.

  • fn:contains. However, if a partial surrogate pair is passed as a value, contains may return unexpected results, since it may find the partial surrogate pair contained in well-formed surrogate pair.

  • fn:concat. However, if a partial surrogate pair is passed as a value, concat can produce incorrect surrogate pairs or partial surrogate pairs.

  • Comparison operators and the order by clause. Comparison operators include +, <, >, <=, >=, eq, lt, gt, le, and ge.

Distributed Query Calls to a System Procedure

Distributed query calls through OPENQUERY to some system procedures will fail when called from one server to another. This occurs when the Database Engine cannot discovery metadata for a procedure. For example, SELECT * FROM OPENQUERY(..., 'EXEC xp_loginfo').

Backward Compatibility

New behavior depends on compatibility level

The following functions and operators demonstrate the new behavior described above only when the compatibility level is 110 or higher:

  • fn:contains.

  • fn:concat.

  • comparison operators and order by clause

New behavior depends on default namespace URI for functions

The following functions demonstrate the new behavior described above only when the default namespace URI corresponds to the namespace in the final recommendation, that is, http://www.w3.org/2005/xpath-functions. When the compatibility level is 110 or higher, then by default SQL Server 2012 binds the default function namespace to this namespace. However these functions demonstrate the new behavior when this namespace is used regardless of the compatibility level.

  • fn:string-length

  • fn:substring

Arrow icon used with Back to Top linkBack to Top

Breaking Changes in SQL Server 2008/SQL Server 2008R2

This section contains the breaking changes introduced in SQL Server 2008. No changes were introduced in SQL Server 2008 R2.

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.

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 .

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

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.

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.

Arrow icon used with Back to Top linkBack to Top

Breaking Changes in SQL Server 2005

For a list of breaking changes introduced in SQL Server 2005, see Breaking Changes to Database Engine Features in SQL Server 2005.

See Also

Reference

Deprecated Database Engine Features in SQL Server 2012

Behavior Changes to Database Engine Features in SQL Server 2012

Discontinued Database Engine Functionality in SQL Server 2012

ALTER DATABASE Compatibility Level (Transact-SQL)

Other Resources

SQL Server Database Engine Backward Compatibility