摘要

Microsoft SQL Server 2016 和 Azure SQL 資料庫中的資料庫引擎,包括資料類型轉換和其他幾個作業的改良功能。 當您使用浮點類型,以及使用傳統 datetime 類型時,大部分的改進功能提供了更大的精確度。

當您使用 至少 130 的資料庫相容性等級時,就可以使用這些改良功能。 這表示,在您將資料庫升級至相容性等級130或較高版本之後,對於某些輸入值,您會看到不同的結果。 這些結果可能會反映在資料庫中的持久化結構中,包括受 檢查 限制性的資料表資料、持久化的計算欄位、參照計算欄的索引、篩選的索引,以及索引視圖。

如果您的資料庫是在舊版 SQL Server 中建立的,我們建議您在升級至 SQL Server 2016 或更新版本之後,以及在變更資料庫相容性層級前,進行額外的驗證。 如果您發現資料庫中的任何持久化結構都會受到這些變更的影響,我們建議您在升級資料庫相容性層級之後,重建受影響的結構。 如此一來,您將會在 SQL Server 2016 或更新版本中受益于這些改進。

本文說明如何將資料庫中的持久化結構驗證為升級至相容性等級130或較高版本,以及在變更相容性層級之後,您可以如何重建任何受影響的結構。

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

從 sql server 2016 開始,SQL Server 和 Azure SQL Database 都包含下列運算精確度的改良功能:

  • 不常見的資料類型轉換。 其中包括下列各項:

    • Float/integer to/from datetime/smalldatetime

    • Real/float to/from numeric/money/smallmoney

    • 浮點數至實數

  • DATEPART /DATEDIFF及度的部分案例DEGREES

  • CONVERT使用Null樣式的CONVERT

若要在您的應用程式中使用這些改進的運算式評估,請將資料庫的相容性層級變更為130(適用于 SQL Server 2016)或140(適用于 SQL Server 2017 和 Azure SQL Database)。 如需所有變更的詳細資訊,以及顯示變更的一些範例,請參閱 附錄 A

資料庫中的下列結構可能會保留運算式的結果:

  • 檢查 限制的表格資料

  • 持久的計算資料行

  • 在主鍵或包含欄中使用計算資料行的索引

  • 已篩選索引

  • 索引視圖

請試想下列案例:

  • 您的資料庫是由舊版 SQL Server 所建立,或是已在 SQL Server 2016 或更新版本中建立,但是相容性層級120或更舊的層級。

  • 您可以使用任何精度已在資料庫中的持久化結構定義中改良的運算式。

在這種情況下,您可能會有由使用相容性等級130或更高版本所實現的精確度改進所影響的持久結構。 如果是這種情況,建議您驗證持久化結構,並重建任何受影響的結構。

如果您有受影響的結構,且在變更相容性層級後沒有重建這些結構,您可能會遇到稍微不同的查詢結果。結果取決於是否使用特定的索引、計算結果欄或視圖,以及是否可認為表格中的資料違反限制.

 

注意: SQL Server 中的追蹤標誌139

在您分析在具有舊版相容性層級的資料庫上的相容性等級130所產生的精確度及轉換邏輯時,在 SQL Server 2016 CU3 和 Service Pack (SP)1中推出了全域追蹤標誌139,以強制進行 DBCC check 命令(例如 DBCC CHECKDBDbcc CHECKTABLEdbcc 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/TABLECHECKCONSTRAINTS 命令。

    3. 執行DBCC TRACEOFF (139,-1)來停用追蹤標誌139。

  2. 將資料庫相容性等級變更為130(針對 SQL Server 2016)或140(適用于 SQL Server 2017 和 Azure SQL Database)。

  3. 重建您在步驟1中發現的任何結構。

注意: Azure SQL 資料庫中的追蹤標誌

Azure SQL Database 不支援設定追蹤旗標。 因此,您必須先變更相容性等級,然後才能執行驗證:

  1. 將資料庫相容性等級升級至140。

  2. 執行驗證以識別任何受影響的持久結構。

  3. 重建您在步驟2中標出的結構。

  • 附錄 A 包含所有精確度改進的詳細清單,並提供每個專案的範例。

  • 附錄 B 包含執行驗證及重建任何受影響結構的詳細逐步程式。

  • 附錄 CD 包含腳本,可協助找出資料庫中可能受影響的物件。 因此,您可以設定驗證的範圍並產生對應的腳本來執行檢查。 若要更輕鬆地判斷相容性等級130的精確度改善是否會影響您資料庫中的任何持久化結構,請在 附錄 D 中執行腳本,以產生正確的驗證檢查,然後執行此腳本來執行驗證。

 

附錄 A:相容性等級130的變更

此附錄提供相容性等級130中運算式評估的詳細清單。每個變更都包含關聯的範例查詢。查詢可以用來顯示在資料庫中執行與使用相容性等級130的資料庫相比,在使用130版相容性等級的資料庫中執行的差異。  

下清單格列出資料類型轉換和其他作業。

 

變更

範例查詢

相容性等級 < 130 的結果

相容性等級 = 130 的結果

浮點數、實數、數位、小數、money 或 smallmoney

datetime 或 Smalldatetime

增加舍入精確度。 先前,日期和時間是單獨轉換的,結果會在您將其合併之前被截斷。

宣告 @f FLOAT = 1。2

宣告 @d DATETIME = @f

選取 [轉換(@d 成浮點數)]

1.19999996141975

1.2

datetime

Bigint、int 或 Smallint

負 datetime,其時間部分正好是半天,或在半天的刻度中舍入時不正確(結果是由1開始)。

宣告 @h DATETIME =-0。5

選取 [@h,轉換(@h 為 INT)]

0

-1

datetime 或 Smalldatetime

float、real、numeric、money 或 smallmoney

在某些情況下,改善最後8位精確度的精確度。

宣告 @p0 DATETIME = "1899-12-31 23:58: 00.470"

宣告 @f FLOAT = CONVERT (FLOAT,@p0)

選取 [@f]、[轉換] (@f 為 VARBINARY (8))

-0.00138344907407406, 0xBF56AA9B21D85800

-0.00138344907407407, 0xBF56AA9B21D8583B

實際

邊界檢查較不嚴格。

選取 [轉換] (3.40282347000 E + 038 為 [實際])

算術溢出

3.402823E+38

數位、貨幣及 smallmoney

當輸入刻度為零時,當您合併數值的四個部分時,會有一個舍入 imprecision。

宣告 @n NUMERIC (38,0) = 41538374868278625639929991208632320

宣告 @f FLOAT = CAST (@n 為浮點數)

選取 [轉換] (BINARY (8),@f)

0x4720000000000000

0x4720000000000001

數位、貨幣及 smallmoney

當輸入比例是非零時,當您除以 10 ^ 比例時,會有一個舍入 imprecision。

宣告 @n NUMERIC (18,10) = 12345678.0123456781

宣告 @f FLOAT = CAST (@n 為浮點數)

選取 [轉換] (@f 二進位(8)))

0x41678C29C06522C4

0x41678C29C06522C3

實數或浮點數

小數

在某些情況下,改善四捨五入精確度。

宣告 @f float = 0.14999999999999999

選取 [轉換] (@f 為數值(1,1))

0.2

0.1

實數或浮點數

小數

當您在某些情況下舍入至超過16位數的數位時,可改善精確度。

宣告 @v decimal (38,18) = 1E-18

選取 [@v

0.000000000000000000

0.000000000000000001

實數或浮點數

金錢或 smallmoney

改善當您在某些情況下轉換大型數位時的精確度。

宣告 @f float = 2SET @f = POWER (@f、49) + POWER (@f,-2)

選取 [轉換] (@f 為貨幣)

562949953421312.2048

562949953421312.25

n(var)字元

小數

超過39個字元的輸入不一定會觸發算術溢出。

宣告 @value Nchar (100) = "1.11111111111111111111111111111111111111"

選取 [轉換] (@value 成十進位(2,1))

算術溢出

1.1

n(var)字元

稍微

支援前置空格和符號。

宣告 @value Nvarchar (100) = "1"

選取 [轉換] (@value 為位)

將 Nvarchar 值 "1" 轉換成 [資料類型 bit] 時,轉換失敗。

1

datetime

時間或 datetime2

當您轉換為具有較高精確度的日期/時間類型時,精確度得到改善。 請注意,datetime 值會儲存為代表 1/300th 秒的刻度。 較新的時間與 datetime2 類型會儲存離散數位數,其中位數符合精確度。

宣告 @value datetime = "1900-01-01 00:00: 00.003"

選取 [轉換] (@value 為時間(7))

00:00:00.0030000

00:00:00.0033333

時間或 datetime2

datetime

改善在某些情況下的舍入。

宣告 @value 時間(4) = "00:00: 00.0045"

選取 [轉換] (@value 為 datetime)

1900-01-01 00:00:00.007

1900-01-01 00:00:00.003

 

一道

變更

範例查詢

相容性等級 <130 的結果

相容性等級130的結果

使用使用數值資料類型的 RADIANS學位 內建函數。

以 pi/180 為單位所整除,先前乘以 180/pi 的角度。 對 弧度而言相似。

宣告 @arg1 數值型 = 1

選取度數(@arg1)

57.295779513082323000

57.295779513082322865

一個運算元的刻度大於結果比例時的數位加法或減法。

[舍入] 總是出現在加法/減法之後,但在前面可能會發生這種情況。

宣告 @p1 numeric (38,2) =-1.15

宣告 @p2 numeric (38,1) = 10

選取 @p1 + @p2

8.8

8.9

轉換 Null 樣式。

當目標型別為數值時,使用null樣式的CONVERT會傳回Null

選取 [轉換] (SMALLINT,"0",Null);

0

NULL

使用 [日期時間] 資料類型的 [微秒] 或 [毫微秒] 選項的DATEPART

在轉換成 [微] 或 [毫微秒] 之前,不會再以毫秒層級截斷值。

宣告 @dt DATETIME = "01-01-1900 00:00: 00.003";

選取 DATEPART (微秒、@dt);

3000

3333

使用 [微秒] 或 [毫微秒] 選項的DATEDIFF ,以及 [datetime] 資料類型。

在轉換成 [微] 或 [毫微秒] 之前,不會再以毫秒層級截斷值。

宣告 @d1 DATETIME = "1900-01-01 00:00: 00.003"

宣告 @d2 DATETIME = "1900-01-01 00:00: 00.007"

選取 DATEDIFF (微秒、@d1、@d2)

3000

3333

比較 datetime 與 datetime2 值與非零值之間的比較(毫秒)。

當您執行與 datetime2 值的比較時,[Datetime] 值不會再以毫秒層級截斷。 這表示先前比較相等的特定值,不再比較相等。

宣告 @d1 DATETIME = "1900-01-01 00:00: 00.003"

宣告 @d2 DATETIME2 (3) = @d1

選取 [CAST] (@d1 為 datetime2 (7)),@d2SELECT CASE IF (@d1 = @d2),然後 "等於" ELSE "不相等" 結束

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

視為

使用 float 資料類型的ROUND函數。

舍入結果有所不同。

選取 [ROUND (-0.4175 成浮點數)],3)

-0.418

-0.417

 

附錄 B:驗證及更新持久結構的步驟

我們建議您判斷資料庫是否有任何會受到相容性等級130變更影響的持久結構,以及重建任何受影響的結構。

請注意,這只適用于在舊版 SQL Server 或使用低於130的相容性等級在資料庫中建立的保留結構。可能受到影響的持久結構包括下列各項:

  • 檢查 限制的表格資料

  • 持久的計算資料行

  • 在主鍵或包含欄中使用計算資料行的索引

  • 已篩選索引

  • 索引視圖

在這種情況下,請執行下列程式。  

  1. 使用在 [ 查看] 或 [變更資料庫的相容性等級] 中所記錄的程式,檢查資料庫的相容性等級。

  2. 如果資料庫相容性等級低於130,我們建議您執行步驟2中所述的驗證,然後再將相容性等級增加到130。

在下列任一禮儀中,判斷資料庫是否包含受相容性等級130中改良之精確度及轉換邏輯影響的任何持久化結構:

  • 含有 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKDB,可驗證資料庫中的所有結構。

  • 使用 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKTABLE,可驗證與單一資料表相關的結構。

需要 使用 EXTENDED_LOGICAL_CHECKS 的選項,才能確定持續值與計算值比較,以及標記有差異的情況。 由於這些檢查範圍很廣,因此使用這個選項的 dbcc 語句執行時間比不含選項執行 dbcc 語句長得多。 因此,大型資料庫的建議是使用 DBCC CHECKTABLE 來精確找出個別的資料表。

DBCC CHECKCONSTRAINTS 可以用來驗證 檢查 限制。 此語句可以在資料庫或資料表層級使用。

請注意, DBCC CHECK * 語句應該總是在維護視窗期間執行,因為檢查線上工作負載可能會受到影響。

資料庫層級驗證

資料庫層級的驗證適用于大小適中且大小適中的資料庫。 針對大型資料庫使用表格層級驗證。

使用EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKDB是用來驗證資料庫中所有的持久化結構。

DBCC CHECKCONSTRAINTS是用來驗證資料庫中的所有CHECK約束。

DBCC CHECKCONSTRAINTS

DBCC CHECKCONSTRAINTS是用來驗證限制的完整性。

使用下列腳本來驗證資料庫:

使用 [database_name]

經過

DBCC TRACEON (139,-1)

經過

DBCC CHECKCONSTRAINTS

經過

DBCC TRACEOFF (139,-1)

經過

使用追蹤標記可確保檢查是使用相容性等級130中改良的精確度和轉換邏輯來執行,即使資料庫的相容性等級較低,也會強制執行正確的轉換語義。

如果 CHECKCONSTRAINTS 語句已完成且沒有傳回結果集,則無需執行任何其他動作。

如果該語句傳回結果集,則結果中的每一行都會指出違反限制,而且也會包含違反限制的值。

  • 儲存資料表與限制的名稱,以及導致違反的值(結果集中的「位置」欄)。

下列範例顯示含有 CHECK 約束的資料表,以及在相容性層級130下符合限制式的單一列,但違反限制。

變更資料庫目前集合 COMPATIBILITY_LEVEL = 120

經過

建立資料表 dbo. table1

(

c2 datetime、

c3 datetime、

c4 int,

CONSTRAINT chk1 CHECK (c4 = (是 DATEDIFF (ms,c2,c3)))

)

經過

插入 dbo. table1 (c2,c3,c4)值

(

convert (datetime,"1900-01-01 00:00: 00.997"),

convert (datetime,"1900-01-01 00:00:01"),3)

經過

DBCC TRACEON (139,-1)

經過

DBCC CHECKCONSTRAINTS

經過

DBCC TRACEOFF (139,-1)

經過  

CHECKCONSTRAINT 命令 returns 下列結果。

Constraint

什麼

[dbo].[table1]

[chk1]

[c2] = "1900-01-01 00:00: 00.997" 和 [c3] = "1900-01-01 00:00: 01.000" 和 [c4] = "3"

此結果表示違反 [Where] 中欄值組合的限制 [chk1]。

含 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKDB

使用 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKDB會驗證資料庫中的所有持久化結構。這是最方便的選項,因為單一語句會驗證資料庫中的所有結構。不過,這個選項不適用於大型資料庫,因為該語句的預期執行時間。

使用下列腳本來驗證整個資料庫:

使用 [database_name]

經過

DBCC TRACEON (139,-1)

經過

含 EXTENDED_LOGICAL_CHECKS、NO_INFOMSGS、TABLERESULTS 的 DBCC CHECKDB

經過

DBCC TRACEOFF (139,-1)

經過

使用追蹤標記可確保檢查是使用相容性等級130中改良的精確度和轉換邏輯來執行,即使資料庫的相容性等級較低,也會強制執行正確的轉換語義。

如果 CHECKDB 語句已順利完成,則不需要其他動作。

如果語句已完成,但有錯誤,請依照下列步驟進行:

  1. 將在 SQL Server Management Studio (SSMS)的 [訊息] 窗格中找到的 DBCC 語句的結果儲存至檔案。

  2. 確認任何報告的錯誤都與持久化結構有關

資料表1:持久的結構及相對應的錯誤訊息,以瞭解不一致  

受影響的結構類型

觀測到的錯誤訊息

記下

持久的計算資料行

Msg 2537,Level 16Table 錯誤:物件識別碼 <object_id>,索引識別碼 <index_id>,。 記錄檢查(有效的計算資料行)失敗。 這些值是。

<object_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。 這包含所影響之確切列的詳細資料。 此練習中可能會忽視這些程式。

<object_id> 與索引識別碼 <index_id> 的物件識別碼

索引視圖

Msg 8908

[索引視圖] <view_name> "(物件識別碼 <object_id>)不會包含視圖定義所產生的所有資料列。

And/or

[Msg 8907The] 索引視圖 ' <view_name> "(<object_id> 的物件識別碼)包含未由視圖定義產生的資料列。

<object_id 的物件識別碼>

完成資料庫層級驗證之後,請移至步驟3。

物件層級驗證

對於較大的資料庫,您可以一次驗證一個資料表或一個視圖的結構和約束,以減少維護視窗的大小,或限制擴展式邏輯檢查,只對可能受影響的物件加以限制。

使用 附錄 C 中的查詢來找出可能受影響的資料表。 附錄 D中的腳本可以用來根據附錄 C中所列的查詢來產生CHECKTABLECHECKCONSTRAINTS的限制。

DBCC CHECKCONSTRAINTS

若要驗證與單一資料表或視圖相關的限制,請使用下列腳本:

使用 [database_name]

經過

DBCC TRACEON (139,-1)

經過

DBCC CHECKCONSTRAINTS ()

經過

DBCC TRACEOFF (139,-1)

經過

使用追蹤旗標可確保檢查是使用相容性等級130中改良的精確度和轉換邏輯來執行,即使資料庫的相容性等級較低,也會強制改善語義。

如果 CHECKCONSTRAINTS 語句已完成且沒有傳回結果集,則無需執行任何其他動作。

如果該語句傳回結果集,則結果中的每一行都會指出違反限制,同時也會提供違反限制的值。

  • 儲存資料表與限制的名稱,以及導致違反的值(結果集中的 Where 資料行)。

含 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKTABLE

若要驗證與單一資料表或視圖相關的持久化結構,請使用下列腳本:

使用 [database_name]

經過

DBCC TRACEON (139,-1)

經過

EXTENDED_LOGICAL_CHECKS、NO_INFOMSGS、TABLERESULTS 的 DBCC CHECKTABLE ()

經過

DBCC TRACEOFF (139,-1)

經過

如果 CHECKTABLE 語句順利完成,則不需要其他動作。

如果語句已完成,但有錯誤,請依照下列步驟進行:

  1. 從在 SSMS 的 [訊息] 窗格中找到的 DBCC 語句,將結果儲存至檔案。

  2. 確認任何報告的錯誤都與 Table 1 中所列的持久化結構相關。

完成資料表層級驗證之後,請移至步驟3。

如果資料庫的相容性層級已經是130,您可以略過這個步驟。

資料庫的相容性層級可以使用下列腳本變更為130:

使用 [database_name]

經過

變更資料庫目前集合 COMPATIBILITY_LEVEL = 130

經過

注意

由於相容性等級130下有 [查詢優化程式] 變更,因此建議您在變更相容性等級之前先啟用查詢存放區。 如需詳細資訊,請參閱 升級至 SQL Server 2016 期間保持效能穩定性

如果在步驟2中執行驗證期間找不到任何不一致,您就完成了升級,而且可以略過這個步驟。

如果在步驟2中發現不一致,必須執行其他動作,才能移除資料庫中的不一致。 所需的動作取決於受影響之結構類型。

重要

只有在資料庫相容性層級變更為130之後,才能在這個步驟中執行修復動作。

備份資料庫(或資料庫)

我們建議您先進行完整的資料庫備份,然後再執行下列各節所述的任何動作。 如果您使用的是 Azure SQL 資料庫,就不需要自行進行備份。您隨時可以使用時間點還原功能,以在任何更新出現錯誤的情況下返回時間。

檢查限制式

修正 CHECK 限制違反需要修改資料表中的資料或 check 約束本身。

從限制的名稱(在步驟2中取得),您可以取得限制式定義,如下所示:

從 sys.check_constraints 選取定義

其中 object_id = OBJECT_ID (N ' constraint_name」)

若要檢查受影響的表格列,您可以使用先前由 DBCC CHECKCONSTRAINTS 語句傳回的位置資訊:

SELECT

從 [schema_name]。[table_name]

其中 Where_clause

您必須更新受影響的資料列或變更限制式定義,以確保不會違反該限制。

更新資料表資料

沒有任何硬性規則可說明資料的更新方式。 一般來說,對於 DBCC CHECKCONSTRAINTS傳回的每個不同的 Where 語句,您都會執行下列 update 語句:

更新 [schema_name]。[table_name]設定 new_column_values

其中 Where_clause

請考慮下列含限制的範例資料表,以及違反相容性等級130中限制的列:

變更資料庫目前集合 COMPATIBILITY_LEVEL = 120

經過

建立資料表 dbo. table1

(

c2 datetime、

c3 datetime、

c4 int,

CONSTRAINT chk1 CHECK (c4 = (是 DATEDIFF (ms,c2,c3)))

)

經過

插入 dbo. table1 (c2,c3,c4)值

(convert (datetime,"1900-01-01 00:00: 00.997"),

convert (datetime,"1900-01-01 00:00:01"),3)

經過

在這個範例中,限制式是簡單的:欄 c4 必須等於與 c2 和 c3 有關的運算式。 若要更新表格,請將此值指派至 c4:

變更資料庫目前集合 COMPATIBILITY_LEVEL = 130

經過

更新 dbo. table1 設定 c4 = datediff (ms,c2,c3)

其中 [c2] = "1900-01-01 00:00: 00.997" 和 [c3] = "1900-01-01 00:00: 01.000" 和 [c4] = "3"

經過

請注意,update 語句中使用的 WHERE 子句會對應 DBCC CHECKCONSTRAINTS傳回的位置資訊。

更新檢查限制式

若要變更 CHECK 約束,您必須先除去並重新建立。 我們建議您在同一個交易中執行這兩個動作,只要有任何更新的限制定義有任何問題。 您可以使用下列 Transact-sql 語句:

開始交易

ALTER TABLE [schema_name]。[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name]。[table_name]

新增限制式 [constraint_name]

CHECK (new_constraint_definition)

認可

經過

下列範例會更新 dbo. table1 中的限制 chk1:

開始交易

ALTER TABLE dbo. table1

[刪除限制 chk1]

ALTER TABLE dbo. table1

新增限制 chk1

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

認可

經過

持久的計算資料行

更新保留的計算資料行最簡單的方式,就是更新由計算資料行所參照的其中一個資料行。 資料行的新值可以與舊值相同,因此操作不會變更任何使用者資料。

Object_id 針對您在步驟2所記下的計算資料行中的不一致,請遵循下列步驟。

  1. 找出計算所得的資料行:

    • 執行下列查詢,以取得所述 object_id 的資料表名稱和持久化計算資料行名稱:

      選取 [QUOTENAME] (name) + N "." + QUOTENAME (t. name) AS "table",

      QUOTENAME (c1. name) AS "持久化計算資料行",

      c1.column_id 為 "computed_column_id",

      定義做為「computed_column_definition」

      從 sys. 表格 t

      在 t.object_id = c1.object_id 上 sys.computed_columns c1 加入

      並且 c1.is_persisted = 1

      在 t.schema_id = s.schema_id 上加入 sys. 架構 s

      其中 t.object_id = object_id

  2. 識別參照的資料行:

    • 執行下列查詢,找出計算資料行所參照的資料行。 請記下其中一個 referenced_column_names:

      選取 [QUOTENAME] (name) + N "." + QUOTENAME (o. name)做為「參照物件」,

      o.type_desc 做為「物件類型」,referenced_minor_id 為 ' referenced_column_id」,c.name 為 ' referenced_column_name」

      從 sys.sql_expression_dependencies sed

      在 sed.referencing_id = c1.object_id 和 sed.referencing_minor_id = c1.column_id 加入 sys.computed_columns c1

      加入 sys. sed.referencing_id = o.object_id 上的物件 o

      在 o.schema_id = s.schema_id 上加入 sys. 架構 s

      在 o.object_id = c.object_id 和 sed.referenced_minor_id = c.column_id 中加入 sys. c 欄

      其中 referencing_class = 1 且 referenced_class = 1 且 referencing_id = object_id,referencing_minor_id = computed_column_id

  3. 執行包含其中一個所參照之資料行的update 語句,以觸發計算所得的資料行更新:

    • 下列語句會觸發計算資料行所參照之資料行的更新,同時觸發計算所得資料行的更新。

      更新 [schema_name]。[table_name]

      將 referenced_column_name = ISNull (referenced_column_name,referenced_column_name)

    • 語句中的 ISNull 運算式的設計方式是,不會變更原始資料行的值,但仍能使用 DB 相容性等級130運算式評估邏輯來更新計算結果欄。

    • 請注意,對於超大型資料表,您可能不想要更新單一事務中的所有列。 在這種情況下,您可以將 WHERE 子句新增至識別列範圍的 update 語句,以成批執行更新。例如,以主鍵為基礎。

  4. 找出參照計算所得資料行的索引。

    選取 [i.name] 做為 [索引名稱]

    從 sys.index_columns ic 加入 sys. 索引 i 位於 ic.object_id = i.object_id 且 ic.index_id = i.index_id

    其中 i.object_id = object_id 且 ic.column_id = computed_column_id

此查詢會識別參照持久計算資料行的任何索引。 必須重建任何這類索引。 若要這樣做,請依照下一節的步驟進行。

[索引]、[已篩選索引] 和 [索引] 視圖

索引中的不一致會對應到步驟2的 DBCC CHECK * 輸出中的錯誤8951和8952(針對表格)或8907和8908(針對 [視圖])。

若要修復這些不一致問題,請在 REPAIR_REBUILD 執行 DBCC CHECKTABLE。 這將修復索引結構,而不會發生任何資料遺失。 不過,資料庫必須處於單使用者模式,且在進行修復時無法供其他使用者使用。

您也可以手動重建受影響的索引。 如果工作負荷無法離線時,就應該使用這個選項,因為索引重建可以做為線上作業(在支援的 SQL Server 版本中)。

重建索引

如果在單一使用者模式中設定資料庫不是選項,您可以使用 [變更索引重建],針對步驟2中識別的每個索引,分別重新建立索引。

使用下列查詢來取得指定 object_id 與 index_id 的資料表和索引名稱。  

選取 [QUOTENAME] (SCHEMA_NAME (o.schema_id)) + N "." + QUOTENAME (o. NAME) AS "table",i.name 為 "index_name"

從 sys. 物件 o JOIN sys. 索引 i 位於 o.object_id = i.object_id

其中 o.object_id = object_id 且 i.index_id = index_id

使用下列語句重建索引:


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

注意 如果您使用的是標準版、Web 版或 Express 版本,則不支援線上索引建立。因此,必須從 ALTER INDEX 語句中移除 [ (ONLINE = 開啟) ] 選項。

下列範例顯示如何重建已篩選的索引:

變更資料庫目前集合 COMPATIBILITY_LEVEL = 120

經過

建立資料表 dbo. 資料表2

(

c2 datetime、

c3 浮點數

)

經過

插入 dbo. 資料表2(c2,c3)值("1899-12-31 23:58: 00.470",-0.00138344907407406)

經過

在資料表2(c2)上建立索引 ix_1

WHERE (c2 =-0.00138344907407406)

經過

在 [dbo] 上變更資料庫的目前集合 COMPATIBILITY_LEVEL = 130GOALTER INDEX ix_1。資料表2重建方式(線上 = 開啟)

經過

如果您有定期維護方案,建議您在排程的維護中加入此索引重建。

使用 DBCC 進行修復

對於與您在步驟2所記下之不一致的索引相關的每個(object_id),請執行下列腳本來執行修復。 此腳本會將資料庫設定為單使用者模式,以進行修復作業。 在最糟糕的情況下,修復會執行完整的索引重建。

使用 [database_name]

經過

使用 [立即回滾] 變更資料庫目前集 SINGLE_USER

經過

DBCC CHECKTABLE (object_id、REPAIR_REBUILD)與 EXTENDED_LOGICAL_CHECKS、NO_INFOMSGS、TABLERESULTS

經過

變更資料庫目前集合 MULTI_USER

經過

附錄 C:識別候選資料表的查詢

下列腳本會識別您可能想要使用 EXTENDED_LOGICAL_CHECKS 的 DBCC CHECKTABLE來驗證的候選資料表,根據相容性等級130中的改良功能所產生的持久化結構和限制使用的資料類型而定。

下列查詢清單詳細說明需要額外驗證的資料表和可能受影響結構的詳細資料。

下列查詢會透過使用受影響的資料類型,或使用任何受影響的內建函數,傳回參照欄的所有索引視圖:

選取 [QUOTENAME] (SCHEMA_NAME (o.schema_id)) + N "." + QUOTENAME (o. name) as "index",QUOTENAME (sed.referenced_schema_name) + N "." + QUOTENAME (sed.referenced_entity_name)作為 "引用的資料行",QUOTENAME (QUOTENAME)為 "資料類型",

--如果資料類型是數值、整數或 money,則唯一可保證額外檢查的案例

--如果視圖定義包含浮點數或 datetime 值,或轉換為此類值,則會使用 DBCC。

s.definition

從 sys.sql_expression_dependencies sed

加入 sys. sed.referencing_id = o.object_id 和 o 的物件 o。 type = N'V "

加入 sys. 索引 i 位於 o.object_id = i.object_id

將 sys.sql_modules s 加入 s.object_id = o.object_id

加入 sys. sed.referenced_id = c.object_id 上的欄 c,並 sed.referenced_minor_id = c.column_idJOIN sys. c.system_type_id = t.system_type_id 上的類型 t

其中 referencing_class = 1 且 referenced_class = 1 及(c.system_type_id

(59--真實

、62--浮點數

、58--Smalldatetime

、61--datetime

、60--金錢

、122--smallmoney

、106--小數

、108--數值

、56--int

,48--Tinyint

,52--Smallint

、41--時間

、127--Bigint

)或 s. [定義] LIKE "% DATEDIFF%"

或 s. [定義] LIKE "% CONVERT%"

或 s. [definition] 贊 "% CAST%"

或 s. [definition] 贊 "% DATEPART%"

或 s. [定義] LIKE "% 度%")

下列查詢會透過使用受影響的資料類型,或使用任何受影響的內建函數,傳回具有參照其他資料行的所有資料表,或是從索引中保留或參照任何一欄。

選取 [QUOTENAME] (sed.referenced_schema_name) + N "." +

QUOTENAME (sed.referenced_entity_name)做為 [含計算資料行的候選資料表],

QUOTENAME (c1. name)做為「計算所得的資料行」、c1.is_persisted、QUOTENAME (c2)做為「已參照的資料行」、t.name 做為「資料類型」

--如果資料類型是數值、整數或 money,則唯一可保證額外檢查的案例

--如果資料行定義包含浮點數或 datetime 值,或轉換為此類值,則會使用 DBCC。

c1.definition

從 sys.sql_expression_dependencies sed

在 sed.referencing_id = c1.object_id 和 sed.referencing_minor_id = c1.column_id 加入 sys.computed_columns c1

在 sed.referenced_id = c2.object_id 和 sed.referenced_minor_id = c2.column_id 中,將 sys. c2 欄加入。

加入 sys. c2.system_type_id = t.system_type_idWHERE referencing_class = 1 且 referenced_class = 1 的類型 t

以及(c2.system_type_id

(59--真實

、62--浮點數

、58--Smalldatetime

、61--datetime

、60--金錢

、122--smallmoney

、106--小數

、108--數值

、56--int

,48--Tinyint

,52--Smallint

、41--時間

、127--Bigint

)或 c1。巨集定義LIKE "% DATEDIFF%"

或 c1。巨集定義LIKE "% CONVERT%"

或 c1。巨集定義LIKE "% DATEPART%"

或 c1。巨集定義LIKE "% 度%")

--資料行是持久的

c1.is_persisted=1

--或資料行包含在索引中

或存在(在 ic.object_id = c1.object_id 和 ic.column_id = c1.column_id 的 sys.index_columns ic 中選取1)

)

下列查詢會傳回含有篩選的索引的所有資料表,這些資料會參照篩選準則中具有受影響資料類型的欄:

選取 [QUOTENAME] (sed.referenced_schema_name) + N "." +

QUOTENAME (sed.referenced_entity_name)做為「含已篩選索引的候選資料表」,

QUOTENAME (i. name)做為「參照索引」,

QUOTENAME (c. name)做為「已參照的資料行」,

t.name 為 "資料類型",

--如果資料類型是數值、整數或 money,則唯一可保證額外檢查的案例

--使用 DBCC 是篩選準則包含浮點數或 datetime 值的位置

i.filter_definition 做為「篩選準則」

從 sys.sql_expression_dependencies sed

加入 sys. 索引 i 位於 sed.referencing_id = i.object_id 並 sed.referencing_minor_id = i.index_id

在 sed.referenced_id = c.object_id 和 sed.referenced_minor_id = c.column_id 中加入 sys. c 欄

加入 sys. c.system_type_id = t.system_type_id 上的類型 t

其中 referencing_class = 7 且 referenced_class = 1 且 i.has_filter = 1

而且 c.system_type_id IN (59--真實

、62--浮點數

、58--Smalldatetime

、61--datetime

、60--金錢

、122--smallmoney

、106--小數

、108--數值

、56--int

,48--Tinyint

,52--Smallint

、41--時間

、127--Bigint

)

 

下列查詢會列出所有具有參照受影響資料類型或內建函數之 check 約束的資料表:

選取 [QUOTENAME] (sed.referenced_schema_name) + N "." +

QUOTENAME (sed.referenced_entity_name)做為「含檢查限制的候選資料表」,

QUOTENAME (c. name)做為 "constraint_name",c. 定義為 "constraint_definition",

QUOTENAME (col name)做為「已參照的資料行」,t.name 為「資料類型」

從 sys.sql_expression_dependencies sed

在 sed.referencing_id = c.object_id 上加入 sys.check_constraints c,並 sed.referencing_class = 1

在 sed.referenced_id = col.object_id 和 sed.referenced_minor_id = col.column_id 中,將 sys. 欄加入欄。

加入 sys. col.system_type_id = t.system_type_id 上的類型 t

其中 referencing_class = 1 且 referenced_class = 1 及(col.system_type_id

(59--真實

、62--浮點數

、58--Smalldatetime

、61--datetime

、60--金錢

、122--smallmoney

、106--小數

、108--數值

、56--int

,48--Tinyint

,52--Smallint

、41--時間

、127--Bigint)

或 c. [定義] LIKE "% DATEDIFF%"

或 c. [定義] LIKE "% CONVERT%"

或 c. [定義] LIKE "% DATEPART%"

或 c. [定義] LIKE "% 度%")

 

附錄 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

Need more help?

擴展您的技能

探索訓練 >

優先取得新功能

加入 MICROSOFT 測試人員 >

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×