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 @strQueryN’@EmpID int’, 1
    Exec sp_executesql @strQueryN’@EmpID int’, 2

— Lets Check execution count for both

Select sqlTxt.textQS.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”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s