SQL Server 和 Azure SQL 資料庫處理某些數據類型和不常見作業的改善

發行項
12/14/2024

本文介紹如何在升級相容性層級中驗證 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/DATEDIFFDEGREES
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 CHECKDBDBCC CHECKTABLE檢查命令的範圍內強制正確轉換語意，以及DBCC CHECKCONSTRAINTS當您在具有舊版相容性層級的資料庫上分析相容性層級 130 引進的改良精確度和轉換邏輯時。

警告

追蹤旗標 139 並非要在生產環境中持續啟用，而且應該用於執行本文中所述之資料庫驗證檢查的唯一用途。因此，在驗證檢查完成之後，應該在相同的會話中使用來停用 dbcc traceoff (139, -1) 它。

從 SQL Server 2016 CU3 和 SQL Server 2016 SP1 開始，支持追蹤旗標 139。

若要升級相容性層級，請遵循下列步驟：

執行驗證以識別任何受影響的持續性結構：
1. 執行 DBCC TRACEON(139, -1)來啟用追蹤旗標 139。
2. 執行 DBCC CHECKDB/TABLE 和 CHECKCONSTRAINTS 命令。
3. 執行 DBCC TRACEOFF(139, -1)來停用追蹤旗標 139。
將資料庫相容性層級變更為 130（適用於 SQL Server 2016）或 140（適用於 SQL Server 2017 和 Azure SQL 資料庫）。
重建您在步驟 1 中識別的任何結構。

注意

Azure SQL 資料庫 Azure SQL 資料庫不支援設定追蹤旗標。因此，您必須先變更相容性層級，才能執行驗證：

將資料庫相容性層級升級至 140。
驗證以識別任何受影響的持續性結構。
重建您在步驟 2 中所識別的結構。

附錄 A 包含所有精確度改進的詳細清單，並提供每個改進的範例。
附錄 B 包含詳細的逐步程式，以執行驗證並重建任何受影響的結構。
附錄 C 和附錄 D 包含腳本，可協助找出資料庫中可能受影響的物件。因此，您可以設定驗證的範圍，併產生對應的腳本來執行檢查。若要更輕鬆地判斷資料庫中是否有任何持續性結構受到相容性層級 130 精確度改善的影響，請在附錄 D 中執行腳本，以產生正確的驗證檢查，然後執行此腳本來執行驗證。

附錄 A：相容性層級 130 的變更

本附錄提供相容性層級 130 運算式評估改善的詳細清單。每個變更都包含相關聯的範例查詢。相較於使用相容性層級 130 的資料庫，查詢可用來顯示使用 130 前相容性層級的資料庫中執行之間的差異。

下表列出數據類型轉換和其他作業。

數據類型轉換

從	至	變更	範例查詢	相容性層級 < 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.00138344907407407,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`	當您轉換成具有較高精確度的日期/時間類型時，已改善精確度。請注意，datetime 值會儲存為代表秒 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` 會使用microseconds或 nanoseconds 選項搭配 datetime 數據類型。	在轉換成微秒或奈秒之前，值不再在毫秒層級截斷。	`DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);`	3000	3333
`DATEDIFF` 會使用microseconds或 nanoseconds 選項搭配 datetime 數據類型。	在轉換成微秒或奈秒之前，值不再在毫秒層級截斷。	`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：驗證資料庫相容性層級

使用檢視或變更資料庫相容性層級中所述的程式，檢查資料庫的相容性層級。
如果資料庫相容性層級低於 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 會傳回下列結果。

Table	條件約束	其中
[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如果語句順利完成，則不需要採取其他動作。

如果語句完成並出現錯誤，請遵循下列步驟：

將 SQL Server Management Studio （SSMS）中訊息窗格中的語句執行 DBCC 結果儲存至檔案。
確認任何回報的錯誤都與持續性結構相關

表 1：保存的結構和對應的錯誤訊息，以致不一致

受影響的結構類型	觀察到的錯誤訊息	記下
保存的計算數據行	Msg 2537，層級 16 數據表錯誤：對象識別碼 <object_id> 、索引標識碼 <index_id> 、。記錄檢查（有效的計算資料行）失敗。值為。	對象標識碼 <object_id> 和索引標識碼 <index_id>
參考索引鍵或內含數據行中的計算數據行篩選索引	Msg 8951 數據表錯誤：資料表 '<table_name>' （標識符 <object_id>）。數據列在索引 '<index_name' 中沒有相符的索引數據列（ID <>index_id>） And/or Msg 8952 數據表錯誤：數據表 '<table_name' （標識符<table_name>>）。索引 '' 中的索引數據列（ID <index_id>）不符合任何數據列。此外，可能有次要錯誤 8955 和/或 8956。這包含所影響之確切數據列的詳細數據。這些可能忽略此練習。	對象標識碼 <object_id> 和索引標識碼 <index_id>
索引檢視	Msg 8908 索引檢視表 '<view_name' （物件標識符<object_id>>）不包含檢視定義產生的所有數據列。 And/or Msg 8907 索引檢視表 '<view_name' （物件標識符<object_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如果語句順利完成，則不需要採取其他動作。

如果語句完成並出現錯誤，請遵循下列步驟：

將 SSMS 中訊息窗格中所找到語句執行 DBCC 的結果儲存至檔案。
確認任何回報的錯誤都與表 1 所列的保存結構有關。
完成數據表層級驗證之後，請移至步驟 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 [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行必須等於涉及和c3的c2表達式。若要更新資料表，請將此值指派給 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 CHECKCONSTRAINTS傳回的 Where 資訊。

更新 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 相關步驟，請遵循這些步驟。

識別計算資料列：

執行下列查詢，以擷取所 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

識別參考的資料列：

執行下列查詢，以識別計算數據行所參考的數據行。記下其中一個參考的數據行名稱：

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

執行包含其中一個 UPDATE 參考資料行的語句，以觸發計算數據行的更新：
- 下列語句會觸發計算數據行所參考之數據行的更新，並觸發計算數據行的更新。
```
UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
```
- ISNULL語句中的表達式會以未變更原始數據行的值的方式製作，同時仍使用 DB 相容性層級 130 運算式評估邏輯確定計算數據行已更新。
- 請注意，對於非常大的數據表，您可能不想更新單一交易中的所有數據列。在這種情況下，您可以將子句新增 WHERE 至可識別數據列範圍的 update 語句，以批次方式執行更新;例如，根據主鍵。

識別參考計算數據行的索引。

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（適用於檢視表）。

若要修復這些不一致的情況，請使用 REPAIR_REBUILD執行 DBCC CHECKTABLE 。這會修復索引結構，而不會遺失任何數據。不過，資料庫必須處於單一使用者模式，因此在修復發生時無法供其他使用者使用。

您也可以手動重建受影響的索引。如果工作負載無法脫機，則應該使用此選項，因為索引重建可以當做 ONLINE 作業執行（在支援的 SQL Server 版本中）。

重建索引

如果以單一使用者模式設定資料庫不是選項，您可以針對步驟 2 中所識別的每個索引，使用 ALTER INDEX REBUILD個別重建索引。

使用下列查詢來取得指定 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)

注意

如果您使用 Standard、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 修復

針對與索引相關的每個（object_id），步驟 2 中指出的不一致，請執行下列腳本來執行修復。此文本會以單一使用者模式設定修復作業的資料庫。在最壞的情況下，修復會執行完整索引重建。

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* 語句的腳本

下列腳本結合上一個附錄中的查詢，並以和 CHECKTABLE 語句的形式CHECKCONSTRAINTS呈現數據表和檢視清單，以簡化結果。

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

共用方式為

SQL Server 和 Azure SQL 資料庫處理某些數據類型和不常見作業的改善

升級至資料庫相容性層級期間的驗證步驟

附錄 A：相容性層級 130 的變更

附錄 B：驗證和更新保存結構的步驟

附錄 C：用來識別候選數據表的查詢

附錄 D：建立 CHECK* 語句的腳本

意見反應

其他資源

共用方式為

SQL Server 和 Azure SQL 資料庫 處理某些數據類型和不常見作業的改善

升級至資料庫相容性層級期間的驗證步驟

附錄 A：相容性層級 130 的變更

附錄 B：驗證和更新保存結構的步驟

附錄 C：用來識別候選數據表的查詢

附錄 D：建立 CHECK* 語句的腳本

意見反應

其他資源

SQL Server 和 Azure SQL 資料庫處理某些數據類型和不常見作業的改善