Utilizzo di sp_executesql
Per eseguire una stringa, è consigliabile utilizzare la stored procedure sp_executesql anziché l'istruzione EXECUTE. Poiché tale stored procedure supporta la sostituzione di parametri, sp_executesql è più versatile dell'istruzione EXECUTE. Poiché inoltre sp_executesql genera piani di esecuzione che è più probabile vengano riutilizzati in SQL Server, sp_executesql è più efficiente dell'istruzione EXECUTE.
Batch autonomi
Una stringa eseguita tramite sp_executesql o l'istruzione EXECUTE viene eseguita come batch autonomo. Tramite SQL Server, l'istruzione o le istruzioni Transact-SQL incluse nella stringa vengono compilate in un piano di esecuzione distinto da quello del batch contenente sp_executesql o l'istruzione EXECUTE. Per i batch autonomi vengono rispettate le regole seguenti:
Le istruzioni Transact-SQL della stringa eseguita tramite sp_executesql o EXECUTE vengono compilate in un piano di esecuzione solo quando si esegue sp_executesql o l'istruzione EXECUTE. Le stringhe vengono analizzate o verificate per rilevare eventuali errori solo quando vengono eseguite. I nomi cui viene fatto riferimento nelle stringhe vengono risolti solo quando le stringhe vengono eseguite.
Le istruzioni Transact-SQL della stringa eseguita non possono accedere alle variabili dichiarate nel batch contenente la stored procedure sp_executesql o l'istruzione EXECUTE. Il batch contenente sp_executesql o l'istruzione EXECUTE non può accedere alle variabili o ai cursori locali definiti nella stringa eseguita.
Se la stringa eseguita contiene un'istruzione USE che modifica il contesto di database, tale modifica risulta valida solo fino al completamento dell'esecuzione di sp_executesql o dell'istruzione EXECUTE.
Tali regole vengono illustrate nell'esecuzione dei due batch seguenti:
/*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
Sostituzione dei valori dei parametri
sp_executesql, diversamente dall'istruzione EXECUTE, consente di sostituire i valori di tutti i parametri specificati nella stringa Transact-SQL. Le stringhe Transact-SQL generate da sp_executesql risultano pertanto più simili tra loro rispetto a quelle generate dall'istruzione EXECUTE. SQL Server Query Optimizer associa con maggior probabilità le istruzioni Transact-SQL di sp_executesql ai piani di esecuzione delle istruzioni eseguite in precedenza, eliminando in tal modo l'overhead associato alla compilazione di un nuovo piano di esecuzione.
Se si utilizza l'istruzione EXECUTE, è necessario convertire tutti i valori dei parametri in formato carattere o Unicode e inserirli quindi nella stringa Transact-SQL:
Se l'istruzione viene eseguita più volte, per ogni esecuzione è necessario compilare una stringa Transact-SQL completamente nuova, anche se l'unica differenza consiste nei valori dei parametri. Questa operazione comporta la generazione di overhead aggiuntivo nei modi seguenti:
La capacità di SQL Server Query Optimizer di associare la nuova stringa Transact-SQL a un piano di esecuzione esistente viene ostacolata dalle continue variazioni dei valori dei parametri nel testo della stringa, soprattutto nel caso di istruzioni Transact-SQL molto complesse.
L'intera stringa deve essere ricompilata per ogni esecuzione.
Ai valori dei parametri diversi da valori di tipo carattere o Unicode è necessario assegnare ogni volta un formato carattere o Unicode.
La stored procedure sp_executesql supporta l'impostazione dei valori dei parametri separatamente dalla stringa 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 presenta i vantaggi aggiuntivi seguenti:
Poiché il testo effettivo dell'istruzione Transact-SQL non cambia tra le due esecuzioni, Query Optimizer associa l'istruzione Transact-SQL della seconda esecuzione al piano di esecuzione generato per la prima istruzione. Di conseguenza SQL Server non deve compilare la seconda istruzione.
La stringa Transact-SQL viene compilata una sola volta.
Il parametro integer viene specificato in formato nativo. Non è pertanto necessario eseguire la conversione in Unicode.
Nota
Per consentire a SQL Server di riutilizzare il piano di esecuzione, è necessario che i nomi degli oggetti inclusi nella stringa dell'istruzione siano completi.
Riutilizzo di piani di esecuzione
Nelle versioni precedenti di SQL Server l'unico modo per poter riutilizzare i piani di esecuzione consiste nel definire le istruzioni Transact-SQL come stored procedure e nell'eseguirle quindi nell'applicazione. Questo metodo, tuttavia, comporta l'esecuzione di numerose operazioni di amministrazione. L'utilizzo di sp_executesql favorisce la riduzione di questo overhead pur consentendo il riutilizzo dei piani di esecuzione in SQL Server. sp_executesql può essere utilizzata al posto delle stored procedure durante l'esecuzione ripetuta di un'istruzione Transact-SQL, nel caso in cui l'unica variazione consiste nei valori dei parametri forniti all'istruzione Transact-SQL. Poiché le istruzioni Transact-SQL restano inalterate e cambiano solo i valori dei parametri, è probabile che SQL Server Query Optimizer possa riutilizzare il piano di esecuzione creato per la prima esecuzione.
Nell'esempio seguente viene compilata ed eseguita un'istruzione DBCC CHECKDB per tutti i database di un server, ad eccezione dei quattro database di sistema.
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
Il driver ODBC SQL Server utilizza sp_executesql per implementare SQLExecDirect quando l'istruzione Transact-SQL eseguita contiene indicatori di parametri associati. In questo modo è possibile estendere i vantaggi offerti da sp_executesql a tutte le applicazioni che utilizzano ODBC o API definite su ODBC, ad esempio RDO. Le applicazioni ODBC esistenti e trasferite in SQL Server acquisiscono automaticamente tutti i vantaggi in termini di prestazioni senza alcuna modifica a livello di programmazione. L'unica eccezione è che la stored procedure sp_executesql non viene utilizzata con parametri data-at-execution. Per ulteriori informazioni, vedere Utilizzo dei parametri delle istruzioni.
Il provider ODBC Native Client di SQL Server utilizza inoltre sp_executesql per implementare l'esecuzione diretta delle istruzioni con parametri associati. Le applicazioni che utilizzano OLE DB o ADO acquisiscono automaticamente i vantaggi di sp_executesql senza alcuna modifica a livello di programmazione.
Vedere anche