SQL兼容级别130与100中的舍入问题

Question

开发人员联系了我，询问舍入问题。

我们正计划将我们的数据库迁移到SQL Server 2016，在新的SQL平台测试期间，我们发现SQL Server 2016在舍入方面略低于预期。

这种差异可能是更改/设置更高兼容级别时默认舍入方法更改的结果。

--SQL Server 2016数据库，兼容模式130

declare @a float = -0.0051175 -- 7 digits
declare @b float = 0.0051175

select round(@a, 6), round(@b, 6)

Result
------------------------------------
-0.005117 , 0.005117

--兼容模式为100的SQL Server 2016数据库

declare @a float = -0.0051175 -- 7 digits
declare @b float = 0.0051175

select round(@a, 6), round(@b, 6)

Result
-----------------------------------
-0.005118   , 0.005118

有人对此有什么想法吗？

Answer 1

在我的博客上-- https://justdaveinfo.wordpress.com/2017/02/27/sql-server-2016-upgrading-to-compatability-level-130trace-flag-139-and-additional-one-off-dbcc-checks/

"https://support.microsoft.com/en-gb/help/4010261/sql-server-2016-improvements-in-handling-some-data-types-and-uncommon-

升级到SQL Server 2016 RTM CU3/SP1和升级到数据库兼容性级别130时，需要执行额外的DBCC检查。

这些信息隐藏在跟踪标志139后面，应在将数据库兼容级别更改为130的过程中临时启用该标志。

Enable trace flag 139 by running DBCC TRACEON(139, -1).
Run DBCC CHECKDB/TABLE..WITH EXTENDED_LOGICAL_CHECKS to validate persisted structures
Run DBCC CHECKCONSTRAINTS commands (if rows are affected the associated where clause to identify the row will be returned).
Disable trace flag 139 by running DBCC TRACEOFF(139, -1)
Change the database compatibility level to 130.
REBUILD any structures that you identified in step 1.

在数据库级130中存在对表达式求值的改进，并且这影响持久结构

Check constraints
Persisted Computed columns
Indexes using computing columns whether as part of the key or as included columns
Filtered indexes
Indexed views

在尝试修复问题之前升级到兼容级别130，以便将新的表达式求值逻辑用于已修复的。

Check constraints – change data or drop/recreate constraint with new expression
Persisted Computed columns – Update a column referenced by the computed column to the same value to force recalcuation of the computed column
Index/filtered index/indexed views – Either A) Put db in single user mode and run DBCC CHECKTABLE with REPAIR_REBUILD B) ALTER INDEX…REBUILD and if supported in your edition of sql server consider adding the WITH (ONLINE=ON) clause.

注意:在上面的附录C/D中有一些查询可以用来帮助识别受影响的对象。“