SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations

Applies to: SQL Server 2016 Enterprise CoreSQL Server 2016 StandardSQL Server 2016 Developer More

Summary


Starting with SQL Server 2016 and Azure SQL Database, the database engine includes improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types. These improvements are all available when you use at least database compatibility level of 130. This means that for some (mostly uncommon) expressions you will see different results for some input values after you upgrade the database to compatibility level 130 or a higher setting. These results may be reflected in persisted structures in the database, included table data subject to CHECK constraints, persisted computed columns, indexes referencing computed columns, filtered indexes, and indexed views.
 
If you have a database that was created in an earlier version of SQL Server, we recommend that you do additional validation after you upgrade to SQL Server 2016 or later, and before you change the database compatibility level. If you find that any of the persisted structures in your database are affected by these changes, we recommend that you rebuild affected structures after you upgrade the database compatibility level. By doing this, you'll benefit from these improvements in SQL Server 2016 or later.
 
This article describes how persisted structures in your database can be validated as part of the upgrade to compatibility level 130 or a higher setting, and how any affected structures can be rebuilt after you change the compatibility level.

Validation steps during an upgrade to database compatibility level


Starting with SQL Server 2016, both SQL Server and Azure SQL Database include improvements to the precision of the following operations:
  • Uncommon data type conversions. These include the following:
    • float/integer to/from datetime/smalldatetime
    • real/float to/from numeric/money/smallmoney
    • float to real
  • Some cases of DATEPART/DATEDIFF and DEGREES
  • CONVERT with a NULL style
 
To use these improvements to expression evaluation in your application, change the compatibility level of your databases to 130 (for SQL Server 2016) or 140 (for SQL Server 2017 and Azure SQL Database). For more information about all the changes and examples showing the changes, see Appendix A.
 
The following structures in the database may persist the results of an expression:

 

  • Table data subject to CHECK constraints
  • Persisted computed columns
  • Indexes that use computed columns in the key or included columns
  • Filtered indexes
  • Indexed views
Consider the following scenario:
  • You have a database that was created by an earlier version of SQL Server, or that was already created in SQL Server 2016 or later but at a compatibility level 120 or an earlier level.
  • You use any expressions whose precision was improved as part of the definition of persisted structures in your database.

In this scenario, you may have persisted structures that are affected by the improvements in precision implemented by using compatibility level 130 or higher. If this is the case, we recommend that you validate the persisted structures and rebuild any structure that's affected.

If you have affected structures and you don't rebuild them after changing the compatibility level, you may experience slightly different query results, depending on whether a particular index, computed column, or view is used, and whether data in a table could be considered a violation of a constraint.

 

 

 
To upgrade the compatibility level, follow these steps:
  1. Perform validation to identify any affected persisted structures:
    1. Enable trace flag 139 by running DBCC TRACEON(139, -1).
    2. Run DBCC CHECKDB/TABLE and CHECKCONSTRAINTS commands.
    3. Disable trace flag 139 by running DBCC TRACEOFF(139, -1).
  2. Change the database compatibility level to 130 (for SQL Server 2016) or 140 (for SQL Server 2017 and Azure SQL Database).
  3. Rebuild any structures that you identified in step 1.
 
 
 
  • Appendix A contains a detailed list of all the precision improvements and provides an example for each.
  • Appendix B contains a detailed step-by-step process to perform validation and to rebuild any affected structures.
  • Appendixes C and D contain scripts to help pinpoint potentially affected objects in the database. Therefore, you can scope your validations and generate corresponding scripts to run the checks. To most easily determine whether any persisted structures in your databases are affected by the precision improvements in compatibility level 130, run the script in Appendix D in order to generate the correct validation checks, and then run this script to perform validation.

Appendix A: Changes in compatibility level 130


In this appendix, you will find detailed lists of the improvements to expression evaluation in compatibility level 130. Each change includes an associated example query. The queries can be used to show the differences between executing in a database that uses a pre-130 compatibility level as compared with a database that uses compatibility level 130.

 
The following tables list data types conversions and additional operations.

 


Appendix B: Steps to verify and update persisted structures


The recommendation is to validate whether the database has any persisted structures that are affected by the changes in compatibility level 130, and to rebuild any affected structures.

Be aware that this applies only to persisted structures that were created in the database on an older version of SQL Server or by using a compatibility level that's earlier than 130. The persisted structures that are potentially affected include the following:

  • Table data subject to CHECK constraints
  • Persisted computed columns
  • Indexes that use computed columns in the key or included columns
  • Filtered indexes
  • Indexed views
In this situation, run the following procedure.

 

 

Appendix C: Queries to identify candidate tables


The following scripts identify candidate tables that you may want to validate by using DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, based on the existence of persisted structures and constraints that use data types affected by the improvements in compatibility level 130.

The following set of queries list details about the tables and potentially affected structures that require additional validation.

 

 

Appendix D: Script to create CHECK* statements


 The following script combines the queries from the previous appendix and simplifies the results by presenting a list of tables and views in the form of CHECKCONSTRAINTS and CHECKTABLE statements.

DECLARE @CRLF nvarchar(10) = CHAR(13) + CHAR(10);DECLARE @sql nvarchar(max) = N'DBCC TRACEON(139,-1); ' + @CRLF ;SELECT @sql += N'DBCC CHECKTABLE (N''' + object_for_checktable + N''') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS; ' + @CRLFFROM(--indexed viewsSELECT DISTINCT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'object_for_checktable'FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id AND o.type = N'V' INNER JOIN sys.indexes AS i ON o.object_id = i.object_id INNER JOIN sys.sql_modules AS s ON s.object_id = o.object_id INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_idWHERE referencing_class = 1 AND referenced_class=1      AND (c.system_type_id IN (  59 --real , 62 --float , 58 --smalldatetime , 61 --datetime , 60 --money , 122 --smallmoney , 106 --decimal , 108 --numeric , 56 --int , 48 --tinyint , 52 -- smallint , 41 --time , 127 --bigint) OR s.[definition] LIKE N'%DATEDIFF%'  OR s.[definition] LIKE N'%CONVERT%'  OR s.[definition] LIKE N'%CAST%'  OR s.[definition] LIKE N'%DATEPART%'  OR s.[definition] LIKE N'%DEGREES%')UNION--persisted computed columnsSELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'FROM sys.sql_expression_dependencies AS sedINNER JOIN sys.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_idINNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_idINNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_idWHERE referencing_class = 1 AND referenced_class = 1     AND (c2.system_type_id IN(  59 --real , 62 --float , 58 --smalldatetime , 61 --datetime , 60 --money , 122 --smallmoney , 106 --decimal , 108 --numeric , 56 --int , 48 --tinyint , 52 -- smallint , 41 --time , 127 --bigint) OR c1.[definition] LIKE N'%DATEDIFF%'  OR c1.[definition] LIKE N'%CONVERT%'  OR c1.[definition] LIKE N'%DATEPART%'  OR c1.[definition] LIKE N'%DEGREES%')AND (-- the column is persistedc1.is_persisted = 1 -- OR the column is included in an indexOR EXISTS (SELECT 1 FROM sys.index_columns AS ic WHERE ic.object_id = c1.object_id AND ic.column_id=c1.column_id))UNION--indexed viewsSELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.indexes AS i ON sed.referencing_id = i.object_id AND sed.referencing_minor_id = i.index_idINNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_idWHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 1AND c.system_type_id IN (  59 --real , 62 --float , 58 --smalldatetime , 61 --datetime , 60 --money , 122 --smallmoney , 106 --decimal , 108 --numeric , 56 --int , 48 --tinyint , 52 -- smallint , 41 --time , 127 --bigint)) AS aSELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLFFROM(SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checkconstraints'FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.check_constraints AS c ON sed.referencing_id = c.object_id AND sed.referencing_class = 1INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_idINNER JOIN sys.types AS t ON col.system_type_id = t.system_type_idWHERE referencing_class = 1 AND referenced_class = 1 AND (col.system_type_id IN (  59 --real , 62 --float , 58 --smalldatetime , 61 --datetime , 60 --money , 122 --smallmoney , 106 --decimal , 108 --numeric , 56 --int , 48 --tinyint , 52 -- smallint , 41 --time , 127 --bigint) OR c.[definition] LIKE N'%DATEDIFF%'  OR c.[definition] LIKE N'%CONVERT%'  OR c.[definition] LIKE N'%DATEPART%'  OR c.[definition] LIKE N'%DEGREES%')) aSET @sql += N'DBCC TRACEOFF(139,-1);';PRINT @sql;--to run the script immediately, use the following command:--EXECUTE sp_executesql @sql;GO