次の方法で共有

一部のデータ型と一般的でない操作の処理における SQL Server と Azure SQL Database の機能強化

  • [アーティクル]

この記事では、SQL Server データベースの永続化された構造体をアップグレード互換性レベルの一部として検証する方法と、互換性レベルをアップグレードした後に影響を受ける構造体を再構築する方法について説明します。

元の製品バージョン: SQL Server 2017、SQL Server 2016
元の KB 番号: 4010261

Microsoft SQL Server 2016 および Azure SQL Database のデータベース エンジンには、データ型変換とその他のいくつかの操作の機能強化が含まれています。 これらの機能強化のほとんどは、浮動小数点型と従来の datetime 型を使用する場合の精度が向上します。

これらの機能強化はすべて、130 以上のデータベース互換性レベルを使用する場合に利用できます。 つまり、一部の (ほとんど一般的でない) 式では、データベースを互換性レベル 130 以上の設定にアップグレードした後、一部の入力値に対して異なる結果が表示されます。 これらの結果は、次のように反映される場合があります。

  • データベース内の永続化された構造体
  • CHECK制約の対象となる含まれるテーブル データ
  • 保存される計算列
  • 計算列を参照するインデックス
  • フィルター選択されたインデックス、およびインデックス付きビュー。

以前のバージョンの SQL Server で作成されたデータベースがある場合は、SQL Server 2016 以降にアップグレードした後、データベース互換性レベルを変更する前に、追加の検証を行うことをお勧めします。

データベース内の永続化された構造体のいずれかがこれらの変更の影響を受ける場合は、データベース互換性レベルをアップグレードした後に、影響を受ける構造体を再構築することをお勧めします。 これを行うことで、SQL Server 2016 以降でこれらの機能強化を利用できます。

この記事では、互換性レベル 130 以降の設定へのアップグレードの一環として、データベース内の永続化された構造を検証する方法と、互換性レベルを変更した後に影響を受ける構造体を再構築する方法について説明します。

データベース互換性レベルへのアップグレード中の検証手順

SQL Server 2016 以降では、SQL Server と Azure SQL Database の両方に、次の操作の精度が向上しています。

  • 一般的でないデータ型の変換。 これには、次のものが含まれます。
    • float/integer to/from datetime/smalldatetime
    • 数値/money/smallmoney 間の実数/浮動小数点数
    • Float to real
  • DATEPART/DATEDIFFやDEGREES
  • CONVERTNULL スタイルを使用する

これらの機能強化をアプリケーションで式の評価に使用するには、データベースの互換性レベルを 130 (SQL Server 2016 の場合) または 140 (SQL Server 2017 および Azure SQL Database の場合) に変更します。 すべての変更と変更を示すいくつかの例の詳細については、「 Appendix A 」セクションを参照してください。

データベース内の次の構造体は、式の結果を保持できます。

  • CHECK制約の対象となるテーブル データ
  • 永続化された計算列
  • キーまたは含まれる列で計算列を使用するインデックス
  • フィルター選択されたインデックス
  • インデックス付きビュー

以下のシナリオについて考えてみます。

  • 以前のバージョンの SQL Server によって作成されたデータベース、または SQL Server 2016 以降のバージョンで既に作成されているが、互換性レベル 120 以前のレベルのデータベースがある。

  • データベース内の永続化された構造体の定義の一部として、精度が向上した式を使用します。

このシナリオでは、互換性レベル 130 以上を使用して実装される精度の向上の影響を受ける永続化された構造体が存在する可能性があります。 その場合は、永続化された構造体を検証し、影響を受けるすべての構造体を再構築することをお勧めします。

構造に影響があり、互換性レベルを変更した後に再構築しない場合は、クエリ結果が若干異なる場合があります。 結果は、特定のインデックス、計算列、またはビューが使用されているかどうか、およびテーブル内のデータが制約違反と見なされるかどうかによって異なります。

Note

SQL Server のトレース フラグ 139

グローバル トレース フラグ 139 は、SQL Server 2016 CU3 および Service Pack (SP) 1 で導入され、以前の互換性レベルを持つデータベースで互換性レベル 130 で導入された精度と変換ロジックの向上を分析するときに、 DBCC CHECKDB、 DBCC CHECKTABLE、 DBCC CHECKCONSTRAINTS などの DBCC チェック コマンドのスコープで正しい変換セマンティクスを強制します。

警告

トレース フラグ 139 は運用環境で継続的に有効にすることを意図したものであり、この記事で説明するデータベース検証チェックを実行する目的でのみ使用する必要があります。 そのため、検証チェックが完了した後、同じセッションで dbcc traceoff (139, -1) を使用して無効にする必要があります。

トレース フラグ 139 は、SQL Server 2016 CU3 および SQL Server 2016 SP1 以降でサポートされています。

互換性レベルをアップグレードするには、次の手順に従います。

  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 Database の場合) に変更します。
  3. 手順 1 で識別したすべての構造体を再構築します。

Note

Azure SQL Database のトレース フラグ設定トレース フラグは、Azure SQL Database ではサポートされていません。 そのため、検証を実行する前に互換性レベルを変更する必要があります。

  1. データベース互換性レベルを 140 にアップグレードします。
  2. 影響を受ける永続化された構造体を特定するために検証します。
  3. 手順 2 で識別した構造体を再構築します。

  • 付録 A には、すべての精度向上の詳細な一覧が含まれており、それぞれの例を示します。

  • 付録 B には、検証を実行し、影響を受ける構造体を再構築するための詳細な手順が含まれています。

  • 付録 C および Appendix D には、データベース内の影響を受ける可能性のあるオブジェクトを特定するのに役立つスクリプトが含まれています。 そのため、検証のスコープを設定し、対応するスクリプトを生成してチェックを実行できます。 データベース内の永続化された構造が互換性レベル 130 の精度の向上の影響を受けるかどうかを最も簡単に判断するには、 Appendix D でスクリプトを実行して正しい検証チェックを生成してから、このスクリプトを実行して検証を実行します。

付録 A: 互換性レベル 130 の変更

この付録では、互換性レベル 130 での式評価の機能強化の詳細な一覧を示します。 各変更には、関連付けられているサンプル クエリが含まれます。 クエリを使用すると、互換性レベル 130 を使用するデータベースと比較して、130 より前の互換性レベルを使用するデータベースでの実行の違いを示すことができます。

次の表に、データ型の変換とその他の操作を示します。

データ型の変換

ソース ターゲット Change クエリの例 互換性レベルの結果 < 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.00138344907407406, 0xBF56AA9B21D85800 -0.00138344907407407, 0xBF56AA9B21D8583B
float real 境界チェックの厳格性は低くなります。 SELECT CAST (3.40282347000E+038 AS REAL) 算術オーバーフロー 3.402823E+38
numeric、money、smallmoney float 入力スケールが 0 の場合、数値の 4 つの部分を組み合わせると丸め不正確になります。 DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f) 0x4720000000000000 0x4720000000000001
numeric、money、smallmoney float 入力スケールが 0 以外の場合、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) nvarchar値 '1' をデータ型ビットに変換するときに変換に失敗しました。 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

操作

操作 Change クエリの例 互換性レベルの結果 <130 互換性レベル 130 の結果
数値データ型を使用する RADIANS または DEGREES 組み込み関数を使用します。 DEGREES は pi/180 で除算され、以前は 180/pi で乗算されています。 RADIANSの場合と同様です。 DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1) 57.295779513082323000 57.295779513082322865
1 つのオペランドの小数点以下桁数が結果の小数点以下桁数よりも大きい場合の数値の加算または減算。 丸め処理は常に加算または減算の後に行われますが、以前は前に発生する場合があります。 DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2 8.8 8.9
CONVERT を NULL スタイルで指定します。 CONVERTNULLスタイルでは、ターゲットの型が数値の場合は常にNULLが返されます。 SELECT CONVERT (SMALLINT, '0', NULL); 0 NULL
DATEPART マイクロ秒またはナノ秒のオプションを使用する datetime データ型。 マイクロまたはナノ秒に変換する前に、ミリ秒レベルで値が切り捨てられなくなりました。 DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt); 3000 3333
DATEDIFF マイクロ秒またはナノ秒のオプションを使用する 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 値と 0 以外の値 (ミリ秒) の比較。 datetime2 値との比較を実行すると、datetime 値がミリ秒レベルで切り捨てられなくなりました。 これは、以前に等しく比較された特定の値が等しく比較されなくなったことを意味します。 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.003333,1900-01-01 00:00:00.003 unequal
ROUNDfloatデータ型を使用する関数。 丸め結果は異なります。 SELECT ROUND(CAST (-0.4175 AS FLOAT), 3) -0.418 -0.417

付録 B: 永続化された構造体を検証および更新する手順

データベースに互換性レベル 130 の変更の影響を受ける永続化された構造があるかどうかを判断し、影響を受ける構造体を再構築することをお勧めします。

これは、古いバージョンの SQL Server 上のデータベースで作成された、または 130 未満の互換性レベルを使用して作成された永続化された構造体にのみ適用されます。 影響を受ける可能性がある永続化された構造体には、次のようなものがあります。

  • CHECK制約の対象となるテーブル データ
  • 永続化された計算列
  • キーまたは含まれる列で計算列を使用するインデックス
  • フィルター選択されたインデックス
  • インデックス付きビュー

このような場合は、次の手順を実行します。

手順 1: データベースの互換性レベルを確認する

  1. View に記載されている手順を使用して、データベースの互換性レベルを確認するか、データベースの互換性レベルを変更します。
  2. データベース互換性レベルが 130 より低い場合は、互換性レベルを 130 に引き上げる前に、手順 2 で説明されている検証を実行することをお勧めします。

手順 2: 影響を受ける永続化された構造体を特定する

次のいずれかの方法で、互換性レベル 130 の精度と変換ロジックの向上の影響を受ける永続化された構造体がデータベースに含まれているかどうかを判断します。

  • DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS: データベース内のすべての構造体を検証します。
  • DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS: 1 つのテーブルに関連する構造を検証します。

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 の制約に違反する 1 行を示しています。

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 コマンドは、次の結果を返します。

テーブル 制約 場所
[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 は、データベース内のすべての永続化された構造体を検証します。 1 つのステートメントでデータベース内のすべての構造が検証されるため、これは最も便利なオプションです。 ただし、このオプションは、ステートメントの実行時が予想されるため、大規模なデータベースには適していません。

次のスクリプトを使用して、データベース全体を検証します。

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: 永続化された構造体と、不整合に対応するエラー メッセージ

影響を受ける構造体の種類 エラー メッセージが表示される メモを取る
永続化された計算列 メッセージ 2537、レベル 16 テーブル エラー: オブジェクト ID <object_id> 、インデックス ID <index_id> 。 レコード チェック (有効な計算列) が失敗しました。 値は . オブジェクト ID <object_id> とインデックス ID <index_id>
キーまたは含まれる列の計算列を参照するインデックス フィルター選択されたインデックス メッセージ 8951 テーブル エラー: テーブル '<table_name>' (ID <object_id>)。 データ行のインデックス '<index_name>' に一致するインデックス行がありません (ID <index_id>) And/or Msg 8952 Table error: table '<table_name>' (ID <table_name>)。 インデックス '' のインデックス行 (ID <index_id>) は、どのデータ行にも一致しません。 さらに、2 次エラー 8955 または 8956 が発生する可能性があります。 これには、影響を受けた正確な行に関する詳細が含まれます。 この演習では、これらは無視される可能性があります。 オブジェクト ID <object_id> とインデックス ID <index_id>
インデックス付きビュー メッセージ 8908 インデックス付きビュー '<view_name>' (オブジェクト ID <object_id>) には、ビュー定義で生成されるすべての行が含まれていません。 And/or Msg 8907 インデックス付きビュー '<view_name>' (オブジェクト ID <object_id>) には、ビュー定義によって生成されなかった行が含まれています。 オブジェクト ID <object_id>

データベース レベルの検証が完了したら、手順 3 に進みます。

オブジェクト レベルの検証

大規模なデータベースの場合は、1 つのテーブルまたは 1 つのビューの構造と制約を一度に検証してメンテナンス期間のサイズを小さくするか、拡張された論理チェックを影響を受ける可能性のあるオブジェクトのみに制限すると便利です。

Appendix C セクションのクエリを使用して、影響を受ける可能性のあるテーブルを特定します。 Appendix D セクションのスクリプトを使用すると、Appendix C セクションに記載されているクエリに基づいてCHECKTABLE制約とCHECKCONSTRAINTS制約を生成できます。

DBCC CHECKCONSTRAINTS

1 つのテーブルまたはビューに関連する制約を検証するには、次のスクリプトを使用します。

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

トレース フラグを使用すると、互換性レベル 130 の改良された精度と変換ロジックを使用してチェックが実行され、データベースの互換性レベルが低い場合でも、改善されたセマンティクスが強制されます。

CHECKCONSTRAINTS ステートメントが完了し、結果セットが返されない場合は、追加のアクションは必要ありません。

ステートメントが結果セットを返す場合、結果の各行は制約違反を示し、制約に違反する値も提供します。

テーブルと制約の名前を、違反の原因となった値 (結果セットの WHERE 列) と共に保存します。

EXTENDED_LOGICAL_CHECKSを使用した DBCC CHECKTABLE

1 つのテーブルまたはビューに関連する永続化された構造体を検証するには、次のスクリプトを使用します。

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

Note

互換性レベル 130 ではクエリ オプティマイザーの変更があるため、互換性レベルを変更する前にクエリ ストアを有効にすることをお勧めします。 詳細については、「クエリ ストア使用シナリオの新しい SQL Server へのアップグレード中のKeep のパフォーマンスの安定性」セクションを参照してください。

手順 4: 永続化された構造体を更新する

手順 2 で実行した検証中に不整合が見つからなかった場合は、アップグレードが完了し、この手順をスキップできます。 手順 2 で不整合が見つかった場合は、データベースから不整合を削除するための追加のアクションが必要です。 必要なアクションは、影響を受ける構造の種類によって異なります。

重要

データベース互換性レベルが 130 に変更された後にのみ、この手順で修復アクションを実行します。

データベース (またはデータベース) をバックアップする

次のセクションで説明するアクションを実行する前に、データベースの完全バックアップを実行することをお勧めします。 Azure SQL Database を使用する場合は、自分でバックアップを作成する必要はありません。更新プログラムに問題が発生した場合に備えて、ポイントインタイム リストア機能を常に使用して、時間をさかのぼって戻すことができます。

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

update ステートメントで使用される WHERE 句は、 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

永続化された計算列

保存された計算列を更新する最も簡単な方法は、計算列によって参照される列の 1 つを更新することです。 操作でユーザー データが変更されないように、列の新しい値は古い値と同じにすることができます。

手順 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. 参照されている列の 1 つを含む UPDATE ステートメントを実行して、計算列の更新をトリガーします。

    • 次のステートメントは、計算列によって参照される列の更新をトリガーし、計算列の更新もトリガーします。

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

    • ステートメント内の ISNULL 式は、元の列の値が変更されないように作成されますが、DB 互換性レベル 130 式の評価ロジックを使用して計算列が更新されるようにします。

    • 非常に大きなテーブルでは、1 つのトランザクション内のすべての行を更新したくない場合があることに注意してください。 このような場合は、たとえば、主キーに基づいて、行の範囲を識別する 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 (ビューの場合) に対応します。

これらの不整合を修復するには、REPAIR_REBUILDでDBCC CHECKTABLEを実行します。 これにより、データが失われることなくインデックス構造が修復されます。 ただし、データベースはシングル ユーザー モードである必要があるため、修復の実行中は他のユーザーが使用できません。

影響を受けるインデックスを手動で再構築することもできます。 インデックスの再構築はオンライン操作 (サポートされている 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)

Note

Standard、Web、または Express の各エディションを使用している場合、オンライン インデックス ビルドはサポートされていません。 したがって、オプション WITH (ONLINE=ON) は、 ALTER INDEX ステートメントから削除する必要があります。

次の例は、フィルター選択されたインデックスの再構築を示しています。

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

)

CHECK 制約

次のクエリは、影響を受けるデータ型または組み込み関数を参照する check 制約を持つすべてのテーブルを一覧表示します。

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* ステートメントを作成するスクリプト

次のスクリプトは、前の付録のクエリを組み合わせ、 CHECKCONSTRAINTS ステートメントと CHECKTABLE ステートメントの形式でテーブルとビューの一覧を表示することで結果を簡略化します。

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