sp_executesql is use for parameterized statements while EXEC does not support Parameterized statements. Parameterized statements is more secured in terms of SQL injection and its better reuse in the plan cache which reduces overhead on the server and boost up performance. Sp_executesql executes a string of Transact-SQL in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.
Suppose, if a query which takes a parameter i.e. “EmpID”, When we run the query with ” EmpID ” as 1 and 2 it would be creating two different cache entry (one each for value 1 and 2 respectively).
— First clear Proc cache
DBCC Freeproccache
Go
— Lets Check
— Using EXEC
Declare @strQuery nvarchar(1000)
Select @strQuery = ‘Select * from Employee where EmpID = ”1”’
Exec (@strQuery)
Select @strQuery = ‘Select * from Employee where EmpID = ”2”’
Exec (@strQuery)
— Using SP_EXECUTESQL
–Declare @strQuery nvarchar(1000)
Select @strQuery = ‘Select * from Employee where EmpID =@EmpID’
Exec sp_executesql @strQuery, N’@EmpID int’, 1
Exec sp_executesql @strQuery, N’@EmpID int’, 2
— Lets Check execution count for both
Select sqlTxt.text, QS.execution_count from sys.dm_exec_query_stats QS
Cross Apply (Select [text] from sys.dm_exec_sql_text(QS.sql_handle)) as sqlTxt
It means for Unparameterised (EXEC ) queries the cached plan is reused only if we ask for the same id again. So the cached plan is not of any major use. In case of SP_EXECUTESQL- means for “Parameterised” queries the cached plan would be created only once and would be reused ‘n’ number of times. Similar to that of a stored procedure. So this would have better performance.
sp_executesql works as “Forced Statement Caching” while EXEC works as “Dynamic String Execution”
WikiDBA 