7

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.

I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.

5

This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server).

Why use EXEC() some of the time when you should be using sp_executesql whenever you have parameters? EXEC() forces you to concatenate all of your variables into a single string, and this makes it ripe for abuse.

I wrote about this in more detail here:

I also wrote about protecting yourself from SQL injection here:

SQL injection is a pretty big deal, and plenty of other people have written about it too.

Finally, be sure when you call system procedures that you use the proper casing to match what's stored in sys.all_objects - it should be all lower case. Otherwise, if your code gets deployed to a case sensitive instance, it will all start failing.

  • It has todo with performance since every SQL which varies only in its parameters will result in a new query parsing and compile (unless simple parameterization kicks in) consuming CPU unnecessarily and filling your plan cache with garbage. – Ochoto Oct 23 '18 at 9:50
  • @Ochoto if you have a lot of different query texts then that will happen regardless of whether you use EXEC('<new query text>'); or EXEC sys.sp_executesql '<new query text>';. To mitigate this you use optimize for ad hoc workloads, you don't choose between EXEC and sp_executesql. – Aaron Bertrand Oct 23 '18 at 10:45
  • 1
    I mean that sp_executesql admits parameterized queries and thus can reuse queries that would only differ in its parameters. – Ochoto Nov 22 '18 at 10:57
3

First of all lets check what both commands mean:
sp_executesql: Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.
exec: Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, CLR (common language runtime) stored procedure, scalar-valued user-defined function, or extended stored procedure. The EXECUTE statement can be used to send pass-through commands to linked servers.

some of the main deferences:

  1. sp_executesql allows for statements to be parameterized, Therefore It’s more secure than EXEC in terms of SQL injection
  2. sp_executesql can leverage cached query plans, The TSQL string is built only one time, after that every time same query is called with sp_executesql, SQL Server retrieves the query plan from cache and reuses it.
  3. Temp tables created in EXEC can not use temp table caching mechanism.

References:
https://blogs.msdn.microsoft.com/turgays/2013/09/17/exec-vs-sp_executesql/
https://msdn.microsoft.com/en-us/library/ms188001.aspx
https://msdn.microsoft.com/en-us/library/ms188332.aspx

Edit:
I found the following article regarding the performance:
The performance is a matter of debate between these two methods for stored procedures. As its name suggests, sp_execute is itself a stored procedure, which stores in the system database. SP_ExecuteSQL must require passing SQL strings to it thus, excepted to showing higher chances of caching, consequently leading to perform better when run for the second or later on times.

In other words, its parametrized dynamic T-SQL encourages its reuse. Moreover sp_execute is supposed having higher chances for avoiding unnecessary compilation while executing a dynamic query over exec(). But some experts take it as misleading as they think for both methods a plan will be cached. In fact, for the non-parametrized queries of SP_ExecuteSQL shows the same characteristics as the later one.

http://www.technovisitors.com/2014/07/SPExecuteSQL-Vs-Execute-SQL-Server.html

0

EXEC uses the ad hoc cache and can cause ad hoc plan bloat, whereas sp_executesql doesn't, depends on how stable your plans are.

  • You meant to say, EXEC will generate execution plan every time query is executed. But SP_EXECUTESQL can cache the execution plan. Correct me if am wrong – Pரதீப் Feb 22 '17 at 9:48
  • I think this may have been true in SQL Server 2000... can you demonstrate an observable difference in any modern versions? – Aaron Bertrand Feb 22 '17 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.