• 아티클

sp_executesql 사용

문자열을 실행하려면 EXECUTE 문 대신 sp_executesql 저장 프로시저를 사용하는 것이 좋습니다. sp_executesql 저장 프로시저는 매개 변수 대체를 지원하므로 EXECUTE보다 융통성이 뛰어납니다. 또한 sp_executesql은 SQL Server에서 재사용이 쉬운 실행 계획을 생성하므로 sp_executesql이 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 문의 실행이 완료될 때까지만 지속됩니다.

다음 두 일괄 처리를 실행하면 위의 내용을 잘 이해할 수 있습니다.

/*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 AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

매개 변수 값 대체

sp_executesql은 Transact-SQL 문자열에 지정된 매개 변수의 매개 변수 값 대체를 지원하지만 EXECUTE 문은 지원하지 않습니다. 그러므로 sp_executesql로 생성된 Transact-SQL 문자열은 EXECUTE 문으로 생성된 것보다 더 비슷합니다. SQL Server 쿼리 최적화 프로그램은 sp_executesql의 Transact-SQL 문을 이전에 실행된 문의 실행 계획과 비교하여 새 실행 계획을 컴파일해야 하는 오버헤드를 줄입니다.

EXECUTE 문을 사용하면 모든 매개 변수 값이 문자 또는 유니코드로 변환되고 Transact-SQL 문자열의 일부가 되어야 합니다.

문이 반복적으로 실행되면 매개 변수에 제공되는 값만 달라도 실행할 때마다 완전히 새로운 Transact-SQL 문자열을 작성해야 합니다. 이렇게 하면 다음과 같은 방법으로 추가 오버헤드가 생성됩니다.

  • 새 Transact-SQL 문자열을 기존 실행 계획과 비교하는 SQL Server 쿼리 최적화 프로그램의 기능은 특히, 복잡한 Transact-SQL 문에서 문자열의 텍스트에 있는 매개 변수 값이 주기적으로 변경되어 방해를 받습니다.

  • 전체 문자열은 실행될 때마다 다시 작성되어야 합니다.

  • 매개 변수 값(문자 또는 유니코드 값 제외)은 실행될 때마다 문자 또는 유니코드 형식으로 캐스팅되어야 합니다.

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 AdventureWorks2008R2.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 문을 첫 번째 실행에서 생성된 실행 계획과 비교해야 합니다. 따라서 SQL Server에서 두 번째 문을 컴파일할 필요가 없습니다.

  • Transact-SQL 문자열이 한 번만 작성됩니다.

  • 정수 매개 변수는 자신의 기본 형식으로 지정됩니다. 유니코드로 변환되지 않아도 됩니다.

    [!참고]

    문의 문자열 개체 이름은 SQL Server가 실행 계획을 재사용할 수 있도록 정규화된 이름이어야 합니다.

실행 계획 재사용

이전 버전의 SQL Server에서 실행 계획을 재사용할 수 있는 유일한 방법은 Transact-SQL 문을 저장 프로시저로 정의하고 응용 프로그램에서 저장 프로시저를 실행하는 것입니다. 이 방법은 응용 프로그램의 추가적인 관리 오버헤드를 생성합니다. sp_executesql을 사용하면 이러한 오버헤드를 줄이는 동시에 SQL Server에서 실행 계획을 재사용하도록 허용할 수 있습니다. Transact-SQL 문을 여러 번 실행할 때는 저장 프로시저 대신 Transact-SQL 문에 제공하는 매개 변수 값만 변경해서 sp_executesql을 사용할 수 있습니다. Transact-SQL 문 자체는 고정되어 있고 매개 변수 값만 변하므로 SQL Server 쿼리 최적화 프로그램은 첫 번째 실행에서 생성하는 실행 계획을 재사용하게 됩니다.

다음 예는 4개의 시스템 데이터베이스를 제외한 서버의 모든 데이터베이스에서 DBCC CHECKDB 문을 작성하고 실행합니다.

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

SQL Server ODBC 드라이버는 실행되는 Transact-SQL 문에 바인딩된 매개 변수 표식이 있을 때 sp_executesql을 사용하여 SQLExecDirect를 구현합니다. 이렇게 하면 sp_executesql에서 제공하는 이점이 ODBC를 사용하거나 ODBC를 통해 정의된 RDO 등의 API를 사용하는 모든 응용 프로그램에까지 확장됩니다. SQL Server로 옮겨진 기존 ODBC 응용 프로그램은 다시 작성하지 않아도 그 성능이 자동으로 향상됩니다. 한 가지 예외는 sp_executesql이 실행 시 데이터 매개 변수와 함께 사용되지 않는다는 점입니다. 자세한 내용은 문 매개 변수 사용를 참조하십시오.

또한 SQL Server Native Client ODBC 공급자는 sp_executesql을 사용하여 바인딩된 매개 변수와 함께 문을 직접 실행합니다. OLE DB 또는 ADO를 사용하는 응용 프로그램은 다시 작성하지 않아도 sp_executesql에서 제공하는 이점을 얻을 수 있습니다.