強制パラメータ化
データベースのすべての SELECT、INSERT、UPDATE、および DELETE ステートメントをパラメータ化するように指定することで、SQL Server の「簡易パラメータ化」の既定動作を無効にできますが、いくつかの制約があります。強制パラメータ化は、ALTER DATABASE ステートメントで PARAMETERIZATION オプションを FORCED に設定することで有効になります。強制パラメータ化を行うと、クエリをコンパイルおよび再コンパイルする頻度を緩和できるので、データベースによってはパフォーマンスが向上します。一般的に POS (point-of-sale) などのアプリケーションから大量のクエリが同時に実行されるデータベースは、強制パラメータ化によりパフォーマンスが向上します。
PARAMETERIZATION オプションを FORCED に設定すると、SELECT、INSERT、UPDATE、および DELETE のステートメントに使用されているリテラル値は、その形式を問わずクエリのコンパイル時にパラメータに変換されます。ただし、次に示すクエリ構造に現れるリテラルは例外です。
INSERT...EXECUTE ステートメント。
ストアド プロシージャ、トリガ、またはユーザー定義関数の本体内のステートメント。これらのルーチンのクエリ プランは既に SQL Server により再利用されています。
クライアント側のアプリケーションで既にパラメータ化されている、準備されたステートメント。
XQuery メソッド呼び出しを含んでいるステートメントを、WHERE 句など通常は引数がパラメータ化されるコンテキストで使用した場合。引数がパラメータ化されないコンテキストでこのメソッドを使用した場合は、ステートメントの残りの部分がパラメータ化されます。
Transact-SQL カーソル内のステートメント (API カーソル内の SELECT ステートメントはパラメータ化されます)。
使用が推奨されなくなったクエリ構造。
ANSI_PADDING または ANSI_NULLS を OFF に設定した状態で実行されているステートメント。
パラメータ化が可能なリテラルが 2,097 を超えるステートメント。
WHERE T.col2 >= @bb など、変数を参照するステートメント。
RECOMPILE クエリ ヒントを含んだステートメント。
COMPUTE 句を含むステートメント。
WHERE CURRENT OF 句を含むステートメント。
また、次のクエリ句はパラメータ化されません。以下の場合については、パラメータ化されないのはこれらの句だけです。同じクエリ内のその他の句では強制パラメータ化が可能な場合もあります。
SELECT ステートメントの <select_list>。サブクエリの SELECT リストおよび INSERT ステートメント内の SELECT リストもこれに該当します。
IF ステートメント内のサブクエリの SELECT ステートメント。
クエリの TOP 句、TABLESAMPLE 句、HAVING 句、GROUP BY 句、ORDER BY 句、OUTPUT...INTO 句、FOR XML 句。
OPENROWSET、OPENQUERY、OPENDATASOURCE、OPENXML、または任意の FULLTEXT 演算子に渡す引数。直接渡すか、サブ式として渡すかは問いません。
LIKE 句の pattern 引数と escape_character 引数。
CONVERT 句の style 引数。
IDENTITY 句内の整数の定数。
ODBC 拡張機能の構文で指定した定数。
演算子 +、-、*、/、および % の引数であり、定数のたたみ込みが可能な式。SQL Server は、強制パラメータ化を行うことができるかどうかを決定する際に、次のいずれかの条件が満たされていれば式で定数のたたみ込みが可能であると見なします。
式に列、変数、およびサブクエリが使用されていない。
式に CASE 句が含まれている。
定数のたたみ込みが可能な式の詳細については、「クエリのパフォーマンスが低下している場合のトラブルシューティング : 基数推定中の定数のたたみ込みおよび式の評価」を参照してください。
クエリ ヒントの句に渡す引数。FAST クエリ ヒントの number_of_rows 引数、MAXDOP クエリ ヒントの number_of_processors 引数、および MAXRECURSION クエリ ヒントの number 引数がこれに該当します。
パラメータ化は個々の Transact-SQL ステートメント レベルで行われます。つまり、バッチ内では個々のステートメントがパラメータ化されます。コンパイルの後、パラメータ化されたクエリは、最初に送信されたバッチのコンテキストで実行されます。クエリの実行プランがキャッシュに残っている場合、sys.syscacheobjects 動的管理ビューの sql 列を参照することでクエリがパラメータ化されているかどうかを判断できます。クエリがパラメータ化されている場合、この列には送信されたバッチのテキストの前に (@1 tinyint) のように、パラメータの名前とデータ型が付加されます。クエリ プランのキャッシュの詳細については、「実行プランのキャッシュと再利用」を参照してください。
.gif "注意") 注意 |
|---|
パラメータ名の規則はありません。特定の命名順序に依存することは避けてください。また、パラメータ名、パラメータ化されるリテラル、およびパラメータ化されたテキストに含まれるスペースは、SQL Server のバージョンおよび Service Pack の適用状況によって異なります。 |
パラメータのデータ型
SQL Server がリテラルをパラメータ化する際に、パラメータは次のデータ型に変換されます。
int データ型の範囲に収まるサイズの整数リテラルは、int 型にパラメータ化されます。それよりも大きな整数リテラルのうち、比較演算子 (<、<=、=、!=、>、>=、!<、!>、<>、ALL、ANY、SOME、BETWEEN、IN など) を伴う述語で使用されているものは numeric(38,0) 型にパラメータ化されます。比較演算子を伴う述語で使用されていないものは、リテラルのサイズに見合う十分な有効桁数があり、小数点以下桁数が 0 の numeric 型にパラメータ化されます。
比較演算子を伴う述語で使用されている固定小数点型のリテラルは、有効桁数が 38 桁で、リテラルのサイズに見合う十分な小数点以下桁数がある numeric 型にパラメータ化されます。比較演算子を伴う述語で使用されていない固定小数点型のリテラルは、リテラルのサイズに見合う十分な有効桁数および小数点以下桁数がある numeric 型にパラメータ化されます。
浮動小数点型のリテラルは float(53) 型にパラメータ化されます。
Unicode 以外の文字列リテラルは、8,000 文字以内の場合は varchar(8000) 型に、8,000 文字を超える場合は varchar(max) 型にパラメータ化されます。
Unicode 文字列リテラルは、4,000 文字以内の場合は nvarchar(4000) 型に、4,000 文字を超える場合は nvarchar(max) 型にパラメータ化されます。
バイナリ リテラルは、8,000 バイト以内の場合は varbinary(8000) 型にパラメータ化されます。8,000 バイトを超える場合は、varbinary(max) 型に変換されます。
money 型のリテラルは、money 型にパラメータ化されます。
強制パラメータ化使用のガイドライン
PARAMETERIZATION オプションを FORCED に設定するときは、次のことを考慮してください。
強制パラメータ化を行うと、クエリをコンパイルするときにクエリ内のリテラル定数がパラメータに変更されます。そのため、クエリ オプティマイザにより、最適なクエリ プランが選択されないことがあります。特に、インデックス付きビューや、計算列のインデックスに合わせてクエリが調整されることはあまりありません。パーティション テーブルおよび分散パーティション ビューに発行するクエリについても、最適なプランが選択されない場合があります。インデックス付きビューおよび計算列のインデックスに大きく依存する環境では、強制パラメータ化を使用しないでください。PARAMETERIZATION FORCED オプションは、熟練したデータベース管理者が、パフォーマンスに悪影響が出ないことを確認した上でのみ使用してください。
複数のデータベースを参照する分散クエリは、そのクエリを実行しているデータベースのコンテキストで PARAMETERIZATION オプションが FORCED に設定されていれば強制パラメータ化を行うことができます。
PARAMETERIZATION オプションを FORCED に設定すると、コンパイル中、再コンパイル中、および実行中のクエリ プランを除くすべてのクエリ プランがデータベースのプラン キャッシュから消去されます。設定の変更時にコンパイルまたは実行されているクエリのプランは、次回そのクエリを実行するときにパラメータ化されます。
PARAMETERIZATION オプションはオンライン操作で設定します。このとき、データベースレベルの排他ロックは必要ありません。
SQL Server データベースの互換性を 80 に設定するか、SQL Server 2005 以降のインスタンスに以前のインスタンスのデータベースをアタッチすると、強制パラメータ化が無効になり (SIMPLE に設定され) ます。
PARAMETERIZATION オプションの現在の設定は、データベースを再アタッチまたは復元するときも維持されます。
単一クエリ、および構文は同じでパラメータ値のみが異なる他の任意のクエリを簡易パラメータ化するように指定することで、強制パラメータ化の動作を無効にできます。逆に、データベースで強制パラメータ化の動作が無効になっている場合に、構文が同じクエリに対してのみ強制パラメータ化の動作を指定することもできます。これを行うにはプラン ガイドを使用します。詳細については、「プラン ガイドを使用したクエリのパラメータ化動作の指定」を参照してください。
| 注意 |
|---|
PARAMETERIZATION オプションを FORCED に設定すると、簡易パラメータ化の場合と比べて、報告されるエラー メッセージに違いが現れる場合があります。まず、簡易パラメータ化よりも多くのエラー メッセージが報告されることがあります。また、エラーが発生した行番号が間違って報告されることがあります。 |