通过

SQL Server 和Azure SQL 数据库处理某些数据类型和不常见操作的改进

  • 项目

本文介绍了如何在升级兼容级别中验证 SQL Server 数据库中的持久结构，以及如何在升级兼容级别后重新生成任何受影响的结构。

原始产品版本： SQL Server 2017、SQL Server 2016
原始 KB 数： 4010261

Microsoft SQL Server 2016 和 Azure SQL 数据库 中的数据库引擎包括数据类型转换和其他几个操作的改进。 使用浮点类型以及经典日期时间类型时，这些改进中的大多数改进都提高了精度。

使用数据库兼容性级别至少为 130 时，这些改进都可用。 这意味着，对于某些（大多数不常见）表达式，在将数据库升级到兼容级别 130 或更高版本设置后，某些输入值可以看到不同的结果。 这些结果可能反映在：

  • 数据库中的持久结构
  • 受约束约束 CHECK 的包含表数据
  • 持久化计算列
  • 引用计算列的索引
  • 筛选索引和索引视图。

如果数据库是在早期版本的 SQL Server 中创建的，建议在升级到 SQL Server 2016 或更高版本之后以及更改数据库兼容性级别之前执行其他验证。

如果发现数据库中的任何持久结构都受这些更改的影响，建议在升级数据库兼容性级别后重新生成受影响的结构。 通过执行此操作，你将受益于 SQL Server 2016 或更高版本中的这些改进。

本文介绍如何在升级到兼容级别 130 或更高版本设置时验证数据库中的持久结构，以及如何在更改兼容性级别后重新生成任何受影响的结构。

升级到数据库兼容性级别的验证步骤

从 SQL Server 2016 开始，SQL Server 和 Azure SQL 数据库包括对以下操作精度的改进：

  • 不常见的数据类型转换。 其中包括：
    • 浮点/整数到/从 datetime/smalldatetime
    • Real/float to/from numeric/money/smallmoney
    • 浮动到真实
  • 某些情况 DATEPART/DATEDIFF 和 DEGREES
  • CONVERT使用样式的NULL

若要将这些改进用于应用程序中的表达式计算，请将数据库的兼容性级别更改为 130（对于 SQL Server 2016）或 140（对于 SQL Server 2017 和 Azure SQL 数据库）。 有关所有更改以及显示更改的一些示例的详细信息，请参阅 附录 A 部分。

数据库中的以下结构可能会保留表达式的结果：

  • 受约束约束的 CHECK 表数据
  • 持久化计算列
  • 在键或包含列中使用计算列的索引
  • 筛选索引
  • 索引视图

假设出现了下面这种情景：

  • 你有由早期版本的 SQL Server 创建的数据库，或者已在 SQL Server 2016 或更高版本中创建但兼容级别为 120 或更低级别的数据库。

  • 使用任何表达式，其精度已改进，作为数据库中持久化结构定义的一部分。

在此方案中，你可能已保留结构，这些结构受使用兼容性级别 130 或更高版本实现的精度改进的影响。 如果是这种情况，我们建议验证持久化结构并重新生成受影响的任何结构。

如果已影响结构，并且更改兼容级别后不会重新生成它们，则可能会遇到略有不同的查询结果。 结果取决于是否使用了特定的索引、计算列或视图，以及表中的数据是否被视为违反约束。

注意

SQL Server 中的跟踪标志 139

全局跟踪标志 139 是在 SQL Server 2016 CU3 和 Service Pack （SP） 1 中引入的，用于在 DBCC 检查命令 DBCC CHECKDB的范围内强制正确转换语义， DBCC CHECKTABLE并在 DBCC CHECKCONSTRAINTS 分析具有早期兼容级别的数据库上引入的改进精度和转换逻辑时。

警告

跟踪标志 139 不应在生产环境中持续启用，应仅用于执行本文中所述的数据库验证检查。 因此，在完成验证检查后，应在同一会话中使用 dbcc traceoff (139, -1) 来禁用它。

从 SQL Server 2016 CU3 和 SQL Server 2016 SP1 开始，支持跟踪标志 139。

若要升级兼容性级别，请执行以下步骤：

  1. 执行验证以识别任何受影响的持久化结构：
    1. 通过运行 DBCC TRACEON(139, -1)启用跟踪标志 139。
    2. 运行 DBCC CHECKDB/TABLE 和 CHECKCONSTRAINTS 命令。
    3. 通过运行 DBCC TRACEOFF(139, -1)禁用跟踪标志 139。
  2. 将数据库兼容性级别更改为 130（对于 SQL Server 2016）或 140（对于 SQL Server 2017 和 Azure SQL 数据库）。
  3. 重新生成在步骤 1 中标识的任何结构。

注意

Azure SQL 数据库不支持Azure SQL 数据库设置跟踪标志中的跟踪标志。 因此，在执行验证之前，必须更改兼容性级别：

  1. 将数据库兼容性级别升级到 140。
  2. 验证以确定任何受影响的持久化结构。
  3. 重新生成在步骤 2 中标识的结构。

  • 附录 A 包含所有精度改进的详细列表，并为每个改进提供一个示例。

  • 附录 B 包含详细的分步过程，用于执行验证并重新生成任何受影响的结构。

  • 附录 C 和 附录 D 包含脚本，以帮助查明数据库中可能受影响的对象。 因此，可以限定验证范围并生成相应的脚本来运行检查。 若要最轻松地确定数据库中的任何持久结构是否受兼容性级别 130 精度改进的影响，请在附录 D 中运行脚本以生成正确的验证检查，然后运行此脚本以执行验证。

附录 A：兼容性级别 130 中的更改

本附录提供了兼容性级别 130 中表达式计算改进的详细列表。 每个更改都包含一个关联的示例查询。 与使用兼容级别 130 的数据库相比，查询可用于显示使用 130 前兼容级别的数据库中执行之间的差异。

下表列出了数据类型转换和其他操作。

数据类型转换

From 操作 更改 示例查询 兼容性级别 < 130 的结果 兼容性级别的结果 = 130
float、real、numeric、decimal、money 或 smallmoney datetime 或 smalldatetime 提高舍入精度。 以前，一天和时间是单独转换的，结果在合并之前被截断。 DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT) 1.19999996141975 1.2
datetime bigint, int, or smallint 一个负日期时间，其时间部分正好是半天或半天的滴答声被错误舍入（结果关闭 1）。 DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT) 0 -1
datetime 或 smalldatetime float, real, numeric, money, or smallmoney 在某些情况下，改进了最后 8 位精度的精度。 DECLARE @p0 DATETIME = '1899-12-31 23:58:00.470' DECLARE @f FLOAT = CONVERT(FLOAT, @p0) SELECT @f, CAST(@f AS VARBINARY(8)) -0.00138344907407407406,0xBF56AA9B21D85800 -0.00138344907407407407,0xBF56AA9B21D8583B
float real 边界检查不太严格。 SELECT CAST (3.40282347000E+038 AS REAL) 算术溢出 3.402823E+38
numeric、money 和 smallmoney float 当输入刻度为零时，在组合数字的四个部分时，会有一个舍入不精确。 DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f) 0x4720000000000000 0x4720000000000001
numeric、money 和 smallmoney float 当输入刻度为非零时，当除以 10^刻度时，有一个舍入不精确。 DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8)) 0x41678C29C06522C4 0x41678C29C06522C3
real 或 float numeric 在某些情况下改进了舍入精度。 DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1)) 0.2 0.1
real 或 float numeric 在某些情况下，舍入到 16 位以上的精度提高了。 DECLARE @v decimal(38, 18) = 1E-18 SELECT @v 0.000000000000000000 0.000000000000000001
real 或 float money 或 smallmoney 在某些情况下，转换大量数字时提高了准确性。 DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money) 562949953421312.2048 562949953421312.25
(n)(var)char numeric 超过 39 个字符的输入不再一定触发算术溢出。 DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1)) 算术溢出 1.1
(n)(var)char bit 支持前导空格和标志。 DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit) 将值“1”转换为 nvarchar 数据类型位时，转换失败。 1
datetime time 或 datetime2 改进了转换为具有更高精度的日期/时间类型的精度。 请注意，日期/时间值存储为表示秒 1/300 的刻度。 较新的时间和 datetime2 类型存储离散数字，其中位数与精度匹配。 DECLARE @value datetime = '1900-01-01 00:00:00.003' SELECT CAST(@value AS time(7)) 00:00:00.0030000 00:00:00.0033333
time 或 datetime2 datetime 在某些情况下改进了舍入。 DECLARE @value time(4) = '00:00:00.0045' SELECT CAST(@value AS datetime) 1900-01-01 00:00:00.007 1900-01-01 00:00:00.003

操作

操作 更改 示例查询 兼容性级别 <130 的结果 兼容性级别 130 的结果
RADIANS使用或DEGREES内置函数，该函数使用数值数据类型。 DEGREES 除以 pi/180，其中以前乘以 180/pi。 类似 RADIANS。 DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1) 57.295779513082323000 57.295779513082322865
当一个操作数的刻度大于结果的刻度时，数值加法或减法。 舍入始终发生在加法或减法之后，而以前它有时可能发生。 DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2 8.8 8.9
CONVERT 带有 NULL 样式。 CONVERT 当目标类型为数字时，具有 NULL 样式的样式始终返回 NULL 。 SELECT CONVERT (SMALLINT, '0', NULL); 0 NULL
DATEPART 使用带有 datetime 数据类型的 microseconds 或 nanoseconds 选项。 在转换为微秒或纳秒之前，该值不再在毫秒级别截断。 DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt); 3000 3333
DATEDIFF 使用带有 datetime 数据类型的 microseconds 或 nanoseconds 选项。 在转换为微秒或纳秒之前，该值不再在毫秒级别截断。 DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME = '1900-01-01 00:00:00.007' SELECT DATEDIFF(MICROSECOND, @d1, @d2) 3000 3333
datetime 和 datetime2 值与非零值之间的比较（以毫秒为单位）。 与 datetime2 值进行比较时，在毫秒级别不再截断日期/时间值。 这意味着以前比较相等的某些值不再相等。 DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME2(3) = @d1 SELECT CAST(@d1 AS datetime2(7)), @d2SELECT CASE WHEN (@d1=@d2) THEN 'equal' ELSE 'unequal' END 1900-01-01 00：00：00.0030000,1900-01-01 00：00：00.003 等于 1900-01-01 00：00：00.0033333,1900-01-01 00：00：00.003 不相等
ROUND 使用数据类型的 float 函数。 舍入结果不同。 SELECT ROUND(CAST (-0.4175 AS FLOAT), 3) -0.418 -0.417

附录 B：验证和更新持久结构的步骤

建议确定数据库是否具有受兼容性级别 130 更改影响的任何持久结构，以及重新生成任何受影响的结构。

这仅适用于在较旧版本的 SQL Server 或使用低于 130 的兼容级别在数据库中创建的持久结构。 可能受影响的持久结构包括：

  • 受约束约束的 CHECK 表数据
  • 持久化计算列
  • 在键或包含列中使用计算列的索引
  • 筛选索引
  • 索引视图

在这种情况下，请运行以下过程。

步骤 1：验证数据库兼容性级别

  1. 使用“视图”中 记录的过程检查数据库的兼容性级别，或更改数据库的兼容性级别。
  2. 如果数据库兼容性级别低于 130，建议先执行步骤 2 中概述的验证，然后再将兼容性级别提高到 130。

步骤 2：确定受影响的持久结构

确定数据库是否包含受兼容性级别 130 中改进的精度和转换逻辑影响的任何持久结构，采用以下任一方式：

  • DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS，用于验证数据库中的所有结构。
  • DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS，用于验证与单个表相关的结构。

必须选择此选项 WITH EXTENDED_LOGICAL_CHECKS ，以确保持久化值与计算值进行比较，以及标记存在差异的情况。 由于这些检查很广泛，因此使用此选项的语句的 DBCC 运行时比不使用此选项的运行 DBCC 语句长。 因此，大型数据库的建议是用于 DBCC CHECKTABLE 查明各个表。

DBCC CHECKCONSTRAINTS 可用于验证 CHECK 约束。 可以在数据库或表级别使用此语句。

DBCC CHECK 语句应始终在维护时段内运行，因为检查对联机工作负荷的潜在影响。

数据库级验证

数据库级别的验证适用于小型和中等大小的数据库。 对大型数据库使用表级验证。

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS 用于验证数据库中的所有持久结构。

DBCC CHECKCONSTRAINTS 用于验证数据库中的所有 CHECK 约束。

DBCC CHECKCONSTRAINTS 用于验证约束的完整性。 使用以下脚本验证数据库：

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

使用跟踪标志可确保通过使用兼容性级别 130 中改进的精度和转换逻辑来执行检查，即使数据库具有较低的兼容性级别，也强制正确的转换语义。

CHECKCONSTRAINTS如果语句已完成且未返回结果集，则无需执行其他操作。

如果语句确实返回结果集，则结果中的每个行都表示违反约束，还包含违反约束的值。

  • 保存表和约束的名称，以及导致违反的值（ WHERE 结果集中的列）。

以下示例显示了一个 CHECK 具有约束的表，以及一行，该行满足较低兼容性级别下的约束，但违反了兼容级别 130 下的约束。

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
    c2 datetime,
    c3 datetime,
    c4 int,
    CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2,c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(
    convert(datetime, '1900-01-01 00:00:00.997'),
    convert(datetime, '1900-01-01 00:00:01'), 3
)
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

该 CHECKCONSTRAINT 命令返回以下结果。

表 约束 Where
[dbo]。[table1] [chk1] [c2] = '1900-01-01 00：00：00.997' AND [c3] = '1900-01-01 00：00：01.000' AND [c4] = '3'

此结果指示“Where”中列值的组合违反了约束 [chk1]。

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS 验证数据库中的所有持久结构。 这是最方便的选项，因为单个语句会验证数据库中的所有结构。 但是，由于语句的预期运行时，此选项不适用于大型数据库。

使用以下脚本验证整个数据库：

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
GO
DBCC TRACEOFF(139, -1)
GO

使用跟踪标志可确保通过使用兼容性级别 130 中改进的精度和转换逻辑来执行检查，即使数据库具有较低的兼容性级别，也强制正确的转换语义。

CHECKDB如果语句成功完成，则无需执行其他操作。

如果语句已完成并出现错误，请执行以下步骤：

  1. 将 SQL Server Management Studio（SSMS）消息窗格中的语句执行 DBCC 结果保存到文件中。
  2. 验证报告的任何错误是否与持久化结构相关

表 1：保留的结构和相应的错误消息，以致不一致

受影响的结构类型 观察到的错误消息 记下
持久化计算列 Msg 2537，级别 16 表错误：对象 ID <object_id> ，索引 ID <index_id> ，。 记录检查（有效的计算列）失败。 值为 . 对象 ID <object_id> 和索引 ID <index_id>
引用键或包含列筛选索引中的计算列的索引 Msg 8951 表错误：表“<table_name>”（ID <object_id>）。 数据行在索引“index_name”中没有匹配的索引行（ID index_id>>） And/or Msg 8952 表错误：表“<<table_name>”（ID <<table_name>）。 索引“”（ID <index_id>）中的索引行与任何数据行不匹配。 此外，可能存在辅助错误 8955 和/或 8956。 这包含有关受影响的确切行的详细信息。 这些可能无视本练习。 对象 ID <object_id> 和索引 ID <index_id>
索引视图 Msg 8908 索引视图“view_name>”<（对象 ID <object_id>）不包含视图定义生成的所有行。 和/或 Msg 8907 索引视图“view_name>”<（对象 ID <object_id>）包含视图定义未生成的行。 对象 ID <object_id>

完成数据库级验证后，请转到步骤 3。

对象级验证

对于较大的数据库，一次验证一个表或一个视图上的结构和约束以减小维护时段的大小，或将扩展逻辑检查限制为可能受影响的对象会很有帮助。

使用附录 C 部分中的查询来确定可能影响的表。 附录 D 部分中的脚本可用于根据附录 C 部分中列出的查询生成CHECKTABLE约束和CHECKCONSTRAINTS约束。

DBCC CHECKCONSTRAINTS

若要验证与单个表或视图相关的约束，请使用以下脚本：

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

使用跟踪标志可确保通过使用兼容性级别 130 中改进的精度和转换逻辑来执行检查，即使数据库具有较低的兼容性级别，也强制改进的语义。

CHECKCONSTRAINTS如果语句已完成且未返回结果集，则无需执行其他操作。

如果语句确实返回结果集，则结果中的每个行都表示违反约束，并提供违反约束的值。

保存表和约束的名称，以及导致违反的值（ WHERE 结果集中的列）。

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

若要验证与单个表或视图相关的持久结构，请使用以下脚本：

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

CHECKTABLE如果语句成功完成，则无需执行其他操作。

如果语句已完成并出现错误，请执行以下步骤：

  1. 将 SSMS 消息窗格中的语句执行 DBCC 结果保存到文件中。
  2. 验证报告的任何错误是否与表 1 中列出的持久结构相关。
  3. 完成表级验证后，请转到步骤 3。

步骤 3：升级到兼容级别 130

如果数据库的兼容级别已为 130，则可以跳过此步骤。

可以使用以下脚本将数据库的兼容性级别更改为 130：

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

注意

由于兼容性级别 130 下存在查询优化器更改，因此建议在更改兼容性级别之前启用查询存储。 有关详细信息，请参阅查询存储使用方案中的“升级到较新的 SQL Server”部分中的“保持性能稳定性”。

步骤 4：更新持久结构

如果在步骤 2 中执行的验证过程中未发现不一致，则已完成升级，可以跳过此步骤。 如果在步骤 2 中找到不一致，则需要执行其他操作才能从数据库中删除不一致。 所需的操作取决于受影响的结构类型。

重要

仅在数据库兼容性级别更改为 130 后，才执行此步骤中的修复操作。

备份数据库（或数据库）

建议在执行以下部分介绍的任何操作之前执行完整数据库备份。 如果使用Azure SQL 数据库，则无需自行备份;始终可以使用时间点还原功能及时返回，以防任何更新出现问题。

CHECK 约束

更正 CHECK 约束冲突需要修改表中的数据或 CHECK 约束本身。

从约束的名称（在步骤 2 中获取），可以获取约束定义，如下所示：

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

若要检查受影响的表行，可以使用语句之前返回 DBCC CHECKCONSTRAINTS 的 Where 信息：

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

必须更新受影响的行或更改约束定义，以确保不违反约束。

更新表数据

没有硬规则说明应如何更新数据。 通常，对于返回 DBCC CHECKCONSTRAINTS的每个不同 Where 语句，将运行以下 update 语句：

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

请考虑以下示例表，其中包含一个约束和一个违反兼容级别 130 中的约束的行：

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
c2 datetime,
c3 datetime,
c4 int,
CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2, c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(convert(datetime, '1900-01-01 00:00:00.997'),
convert(datetime, '1900-01-01 00:00:01'), 3)
GO

在此示例中，约束非常简单。 列 c4 必须等于涉及 c2 和 c3的表达式。 若要更新表，请将此值 c4分配给：

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
UPDATE dbo.table1 SET c4 = datediff (ms, c2,c3)
WHERE [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'
GO

请注意， WHERE update 语句中使用的子句对应于返回的 DBCC CHECKCONSTRAINTSWhere 信息。

更新 CHECK 约束

若要更改 CHECK 约束，必须删除并重新创建它。 建议在同一事务中同时执行这两项操作，以防更新的约束定义出现问题。 可以使用以下 Transact-SQL：

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

The following example updates the constraint chk1 in dbo.table1:

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

CHECK (c4 <= DATEDIFF (ms, c2, c3))

COMMIT

GO

持久化计算列

更新持久化计算列的最简单方法是更新计算列引用的列之一。 列的新值可以与旧值相同，这样操作就不会更改任何用户数据。

针对在步骤 2 中记录的计算列中的每一个 object_id 不一致问题，请按照以下步骤操作。

  1. 标识计算列：

    • 运行以下查询，检索已记录 object_id的持久计算列的表名和名称：

      SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS 'table',
QUOTENAME(c1.name) AS 'persisted computed column',
c1.column_id AS 'computed_column_id' ,
definition AS 'computed_column_definition'
FROM sys.tables t
JOIN sys.computed_columns c1 ON t.object_id=c1.object_id
AND c1.is_persisted=1
JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.object_id=object_id

  2. 标识引用的列：

  • 运行以下查询以标识计算列引用的列。 记下引用的列名称之一：

    SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS 'referencing object',
o.type_desc AS 'object type', referenced_minor_id AS 'referenced_column_id', c.name AS 'referenced_column_name'
FROM sys.sql_expression_dependencies sed
JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id
JOIN sys.objects o ON sed.referencing_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
JOIN sys.columns c ON o.object_id=c.object_id AND sed.referenced_minor_id=c.column_id
WHERE referencing_class=1 AND referenced_class=1 AND referencing_id=object_id AND referencing_minor_id=computed_column_id

  1. UPDATE运行涉及其中一个引用列的语句以触发计算列的更新：

    • 以下语句将触发计算列引用的列的更新，并触发计算列的更新。

      UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)

    • ISNULL语句中的表达式以未更改原始列的值的方式进行创建，同时仍确保使用 DB 兼容性级别 130 表达式计算逻辑更新计算列。

    • 请注意，对于非常大的表，你可能不希望更新单个事务中的所有行。 在这种情况下，可以通过将子 WHERE 句添加到用于标识行范围的 update 语句（例如，基于主键）来批量运行更新。

  2. 标识引用计算列的索引。

    SELECT i.name AS [index name]
FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
WHERE i.object_id=object_id AND ic.column_id=computed_column_id

此查询标识引用持久化计算列的任何索引。 必须重新生成任何此类索引。 为此，请按照以下部分中的步骤操作。

索引、筛选索引和索引视图

索引中的不一致对应于步骤 2 输出中的 DBCC CHECK 错误 8951 和 8952（对于表）或 8907 和 8908（对于视图）。

若要修复这些不一致， DBCC CHECKTABLE 请使用 REPAIR_REBUILD. 这将修复索引结构，而不会丢失任何数据丢失。 但是，数据库必须处于单用户模式，因此在修复发生时对其他用户不可用。

还可以手动重新生成受影响的索引。 如果工作负荷无法脱机，则应使用此选项，因为索引重新生成可以作为 ONLINE 操作执行（在受支持的 SQL Server 版本中）。

重建索引

如果单用户模式下设置数据库不是一个选项，则可以使用 ALTER INDEX REBUILD步骤 2 中标识的每个索引单独重新生成索引。

使用以下查询获取给定 object_id 和 index_id的表和索引名称。

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'table', i.name AS 'index_name'

FROM sys.objects o JOIN sys.indexes i ON o.object_id=i.object_id

WHERE o.object_id = object_id AND i.index_id = index_id

使用以下语句重新生成索引：

ALTER INDEX index_name ON [schema_name].[table_name] REBUILD WITH (ONLINE=ON)

注意

如果使用标准版、Web 版或 Express 版本，则不支持联机索引生成。 因此，必须从ALTER INDEX语句中删除该选项WITH (ONLINE=ON)。

以下示例演示如何重新生成筛选索引：

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table2
(
    c2 datetime,
    c3 float
)
GO
INSERT dbo.table2 (c2,c3) VALUES ('1899-12-31 23:58:00.470', -0.00138344907407406)
GO
CREATE INDEX ix_1 ON dbo.table2(c2)
WHERE (c2=-0.00138344907407406)
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GOALTER INDEX ix_1 ON [dbo].[table2] REBUILD WITH (ONLINE=ON)
GO

如果你有定期维护计划，建议将此索引重新生成作为计划维护的一部分。

使用 DBCC 进行修复

对于与步骤 2 中指出的索引相关的每个（object_id），请运行以下脚本来执行修复。 此脚本在单用户模式下为修复操作设置数据库。 在最坏的情况下，修复会执行完整索引重新生成。

USE [database_name]

GO

ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKTABLE (object_id, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

ALTER DATABASE CURRENT SET MULTI_USER

GO

附录 C：用于标识候选表的查询

以下脚本根据存在持久化结构和使用受兼容性级别 130 改进影响的数据类型的约束来标识你可能希望通过使用 DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS候选表进行验证。

以下一组查询列出了有关表和可能需要额外验证的潜在影响结构的详细信息。

索引视图

以下查询通过使用受影响的数据类型或使用任何受影响的内置函数返回引用列的所有索引视图：

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'view', QUOTENAME(i.name) AS 'index',QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'referenced table', QUOTENAME(c.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the view definition contains a float or datetime value, or a conversion to such value

s.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.objects o ON sed.referencing_id = o.object_id AND o.type=N'V'

JOIN sys.indexes i ON o.object_id=i.object_id

JOIN sys.sql_modules s ON s.object_id=o.object_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_idJOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE 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 '%DATEDIFF%'

OR s.[definition] LIKE '%CONVERT%'

OR s.[definition] LIKE '%CAST%'

OR s.[definition] LIKE '%DATEPART%'

OR s.[definition] LIKE '%DEGREES%')

持久化计算列

以下查询返回使用受影响的数据类型引用其他列的计算列的所有表，或使用任何受影响的内置函数（其中列是持久保存或从索引引用的）。

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with computed column',

QUOTENAME(c1.name) AS 'computed column', c1.is_persisted,QUOTENAME(c2.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the column definition contains a float or datetime value, or a conversion to such value

c1.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id

JOIN sys.columns c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id=c2.column_id

JOIN sys.types 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 '%DATEDIFF%'

OR c1.[definition] LIKE '%CONVERT%'

OR c1.[definition] LIKE '%DATEPART%'

OR c1.[definition] LIKE '%DEGREES%')

AND (

-- the column is persisted

c1.is_persisted=1

-- OR the column is included in an index

OR EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id=c1.object_id AND ic.column_id=c1.column_id)

)

筛选索引

以下查询返回包含筛选索引的所有表，这些表引用了影响数据类型的筛选条件中的列：

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with filtered index',

QUOTENAME(i.name) AS 'referencing index',

QUOTENAME(c.name) AS 'referenced column',

t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is where the filter condition contains a float or datetime value

i.filter_definition AS 'filter condition'

FROM sys.sql_expression_dependencies sed

JOIN sys.indexes i ON sed.referencing_id=i.object_id AND sed.referencing_minor_id=i.index_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_id

JOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=7 AND referenced_class=1 AND i.has_filter=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

)

检查约束

以下查询列出了引用受影响的数据类型或内置函数的检查约束的所有表：

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with check constraint',

QUOTENAME(c.name) AS 'constraint_name', c.definition AS 'constraint_definition',

QUOTENAME(col.name) AS 'referenced column', t.name AS 'data type'

FROM sys.sql_expression_dependencies sed

JOIN sys.check_constraints c ON sed.referencing_id=c.object_id AND sed.referencing_class=1

JOIN sys.columns col ON sed.referenced_id=col.object_id AND sed.referenced_minor_id=col.column_id

JOIN sys.types t ON col.system_type_id=t.system_type_id

WHERE 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 '%DATEDIFF%'

OR c.[definition] LIKE '%CONVERT%'

OR c.[definition] LIKE '%DATEPART%'

OR c.[definition] LIKE '%DEGREES%')

附录 D：用于创建 CHECK* 语句的脚本

以下脚本结合了上一个附录中的查询，并通过以表和语句的形式CHECKCONSTRAINTSCHECKTABLE呈现表和视图列表来简化结果。

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; ' + @CRLF
FROM
(

--indexed views
SELECT 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_id

WHERE 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 columns
SELECT 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.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_id
INNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_id
INNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_id
WHERE 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 persisted
c1.is_persisted = 1 
-- OR the column is included in an index
OR 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 views
SELECT 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_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_id
WHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 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
)) AS a

SELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLF
FROM
(

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 = 1
INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_id
INNER JOIN sys.types AS t ON col.system_type_id = t.system_type_id
WHERE 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%')
) a

SET @sql += N'DBCC TRACEOFF(139,-1);';

PRINT @sql;

--to run the script immediately, use the following command:
--EXECUTE sp_executesql @sql;
GO