Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Breaking Changes to Database Engine Features in SQL Server 2016

Breaking Changes to Database Engine Features in SQL Server 2016

 

This topic describes breaking changes in the SQL Server 2016 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.

The sample_ms column of sys.dm_io_virtual_file_stats has expanded from an int to a bigint data type.

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.

WITHIN reserved keyword

WITHIN is now a reserved keyword. References to objects or columns named 'within' will fail. Rename the object or column name or delimit the name by using brackets or quotes. For example, SELECT * FROM [within].

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

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.

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.

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

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.

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.

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 through OPENQUERY to some system procedures will fail when called from one SQL Server 2012 server to another. This occurs when the Database Engine cannot discovery metadata for a procedure. For example, SELECT * FROM OPENQUERY(..., 'EXEC xp_loginfo').

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

Community Additions

ADD
Show:
© 2015 Microsoft