• [アーティクル]

sp_executesql の使用

文字列を実行するには、EXECUTE ステートメントではなく sp_executesql ストアド プロシージャを使用することをお勧めします。sp_executesql ではパラメータの置換がサポートされるので、EXECUTE よりも汎用性が高くなります。また、sp_executesql では、SQL Server が再利用できる可能性の高い実行プランが生成されるので、EXECUTE よりも効率的です。

自己完結型バッチ

sp_executesql または EXECUTE ステートメントを使用して文字列を実行する場合、その文字列は独自の自己完結型バッチとして実行されます。SQL Server により、文字列内の Transact-SQL ステートメントがコンパイルされ実行プランが作成されますが、この実行プランは、sp_executesql または EXECUTE ステートメントを含んでいた元のバッチの実行プランとは別のものです。自己完結型バッチには、次の規則が適用されます。

  • sp_executesql または EXECUTE 文字列内の Transact-SQL ステートメントは、sp_executesql または EXECUTE ステートメントが実行されるまでは実行プランにコンパイルされません。また、文字列が実行されるまでは、文字列の解析もエラーのチェックも行われません。さらに、文字列内で参照される名前も、文字列が実行されるまでは解決されません。

  • 実行される文字列内の Transact-SQL ステートメントからは、sp_executesql または EXECUTE ステートメントを含むバッチ内で宣言された変数にアクセスできません。また、sp_executesql または EXECUTE ステートメントを含むバッチからは、実行される文字列内で定義された変数やローカル カーソルにアクセスできません。

  • 実行される文字列内に、データベース コンテキストを変更するような USE ステートメントが含まれている場合、データベース コンテキストへの変更内容が有効であるのは sp_executesql または EXECUTE ステートメントの実行が完了するまでの間のみです。

上記の規則を適用した 2 つのバッチの実行例を次に示します。

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

パラメータ値の置換

sp_executesql では、Transact-SQL 文字列内で指定したパラメータについてパラメータ値の置換がサポートされますが、EXECUTE ステートメントではサポートされません。このため、EXECUTE ステートメントによって生成される文字列に比べると、sp_executesql によって生成される Transact-SQL 文字列の方が似通ったものになる可能性が高くなります。SQL Server クエリ オプティマイザによる照合で、sp_executesql によって生成される Transact-SQL ステートメントと、以前に実行したステートメントから生成された実行プランが一致しやすくなり、新しい実行プランをコンパイルした場合のオーバーヘッドを節約できます。

EXECUTE ステートメントでは、すべてのパラメータ値を文字または Unicode に変換し、Transact-SQL 文字列の一部にする必要があります。

同じステートメントを繰り返し実行する場合、パラメータ値以外が同じであっても、実行のたびにまったく新しい Transact-SQL 文字列を構築する必要があります。これにより、次の点でオーバーヘッドが増加します。

  • 文字列のテキスト (特に、複雑な Transact-SQL ステートメント) に含まれるパラメータ値は常に変化するので、新しい Transact-SQL 文字列と既存の実行プランを照合する SQL Server オプティマイザの機能が妨げられます。

  • 実行のたびに文字列全体を再構築する必要があります。

  • 文字値または Unicode 値以外のパラメータ値は、実行のたびに文字形式または Unicode 形式にキャストする必要があります。

次のように、sp_executesql では、Transact-SQL 文字列とは別にパラメータ値を設定できます。

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql には、この他に次のような利点もあります。

  • Transact-SQL ステートメントの実際のテキストが実行のたびに変化しないので、クエリ オプティマイザでは、Transact-SQL ステートメントを 2 回目に実行する際に、そのステートメントの初回実行時に生成された実行プランを照合できます。このため、SQL Server が 2 回目のステートメントをコンパイルする必要がなくなります。

  • Transact-SQL 文字列の構築が 1 回で済みます。

  • 整数パラメータは、ネイティブ形式で指定します。Unicode に変換する必要はありません。

    注意注意

    SQL Server によって実行プランが再利用されるようにするには、ステートメント文字列内のオブジェクト名を完全修飾名にする必要があります。

実行プランの再利用

以前のバージョンの SQL Server では、実行プランを再利用するには、Transact-SQL ステートメントをストアド プロシージャとして定義し、そのストアド プロシージャをアプリケーションで実行するしかありませんでした。このため、アプリケーションの管理オーバーヘッドが余分に必要でした。sp_executesql を使用すると、このオーバーヘッドを軽減しながら、SQL Server による実行プランの再利用も可能になります。Transact-SQL ステートメントに指定されたパラメータ値のみを変更する場合、Transact-SQL ステートメントを複数回実行するときに sp_executesql をストアド プロシージャの代わりに使用できます。変わるのはパラメータ値のみであり、Transact-SQL ステートメント自体は変わらないので、SQL Server クエリ オプティマイザで、ステートメントの初回実行時に生成された実行プランを再利用できる可能性が高くなります。

次の例では、DBCC CHECKDB ステートメントを構築し、サーバーにある 4 つのシステム データベース以外のすべてのデータベースについてそのステートメントを実行します。

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY'
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO

実行中の Transact-SQL ステートメントに、バインド済みのパラメータ マーカーが含まれている場合、SQL Server ODBC ドライバは sp_executesql を使用して SQLExecDirect を実装します。このため、ODBC や、ODBC に対応するように定義された API (RDO など) を使用するすべてのアプリケーションでも、sp_executesql による利点が得られます。既存の ODBC アプリケーションを SQL Server に移植した場合は、コードを書き換えなくても、自動的にパフォーマンスが向上します。ただしこれには、sp_executesql に実行時データ パラメータを併用しないという例外があります。詳細については、「ステートメント パラメータの使用」を参照してください。

また、SQL Server Native Client ODBC プロバイダでは、sp_executesql を使用して、パラメータがバインドされているステートメントの直接的な実行を実装します。OLE DB または ADO を使用するアプリケーションでは、コードを書き換えなくても、sp_executesql による利点を得ることができます。