{"id":518,"date":"2010-04-05T21:51:00","date_gmt":"2010-04-05T21:51:00","guid":{"rendered":"\/blogs\/kimberly\/post\/EXEC-and-sp_executesql-how-are-they-different.aspx"},"modified":"2015-11-03T10:40:45","modified_gmt":"2015-11-03T18:40:45","slug":"exec-and-sp_executesql-how-are-they-different","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/exec-and-sp_executesql-how-are-they-different\/","title":{"rendered":"EXEC and sp_executesql &#8211; how are they different?"},"content":{"rendered":"<p>In my last post: <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/little-bobby-tables-sql-injection-and-execute-as\/\" target=\"_blank\">Little Bobby Tables, SQL Injection and EXECUTE AS<\/a>, I wanted to highlight how to avoid SQL Injection\u00a0when using EXEC. A few people\u00a0brought up the\u00a0point that I could have avoided SQL Injection by using sp_executesql. And, while sp_executesql can be used in some cases, it&#8217;s not always the most ideal from a performance perspective. So, to really show this, I&#8217;m going to start with focusing on the similarities and differences of EXEC and sp_executesql. I&#8217;ll start here with some performance details but you&#8217;ll find that I&#8217;m going to just hit the tip of the iceberg with this one. I&#8217;ll definitely need another post or two!<\/p>\n<p>First, a quick overview:<\/p>\n<p><strong>sp_executesql\u00a0(also known as\u00a0&#8220;Forced Statement Caching&#8221;)<\/strong><\/p>\n<ul>\n<li>Allows for statements to be parameterized.<\/li>\n<li>Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs. I&#8217;ll give more details on this in additional posts.<\/li>\n<li>Has strongly typed variables\/parameters &#8211; and this can reduce injection and offer some performance benefits!<\/li>\n<li>Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan<\/li>\n<\/ul>\n<p><strong>EXEC\u00a0 (also known as &#8220;Dynamic String Execution&#8221; or DSE)<\/strong><\/p>\n<ul>\n<li>Allows *any* construct to be built.<\/li>\n<li>Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do &#8211; they are parsed, probably parameterized and possibly deemed &#8220;safe&#8221; for subsequent executions to re-use.<\/li>\n<li>Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed (I have ways around this.)<\/li>\n<li>Does not force a plan to be cached.\n<ul>\n<li>This can be a pro in that SQL Server can create a plan for each execution.<\/li>\n<li>This can be a con in that SQL Server needs to recompile\/optimize for each execution.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Let&#8217;s start with using sp_executesql to parameterize a query where SQL Server would also allow parameters:<\/p>\n<p>DECLARE @ExecStr NVARCHAR(4000);<br \/>\nSELECT @ExecStr = &#8216;SELECT * FROM dbo.member WHERE lastname LIKE @lastname&#8217;;<br \/>\nEXEC sp_executesql @ExecStr, N&#8217;@lastname varchar(15)&#8217;, &#8216;Tripp&#8217;;<br \/>\nGO<\/p>\n<p>Because &#8216;Tripp&#8217; is a highly selective name, SQL Server uses an index to the lookup of the data:<\/p>\n<div style=\"text-align: center;\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/4\/sp_executesql%20plan%201.png\" alt=\"\" width=\"780\" height=\"284\" \/><\/div>\n<p>So, for our next execution, I&#8217;ll supply a different lastname &#8211; a lastname of Anderson. In this database, Anderson is NOT highly selective:<\/p>\n<p>DECLARE @ExecStr NVARCHAR(4000);<br \/>\nSELECT @ExecStr = &#8216;SELECT * FROM dbo.member WHERE lastname LIKE @lastname&#8217;;<br \/>\nEXEC sp_executesql @ExecStr, N&#8217;@lastname varchar(15)&#8217;, &#8216;Anderson&#8217;;<br \/>\nGO<\/p>\n<p>However, the query plan looks exactly the same:<\/p>\n<div style=\"text-align: center;\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/4\/sp_executesql%20plan%202%20-%20anderson.png\" alt=\"\" width=\"825\" height=\"304\" \/><\/div>\n<p>Or does it? It turns out that this query plan does look a tiny bit different but it&#8217;s not very obvious&#8230; it&#8217;s in the thickness of the lines. In this second execution it clearly shows that more data is being passed between the steps. But, is this a bad thing? Maybe, maybe not. Let&#8217;s drill in a bit deeper. If I hover over the Index Seek (on member.test), I can see the following tooltip:<\/p>\n<div style=\"text-align: center;\"><img decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/4\/estimated%20v%20actual%20rows.png\" alt=\"\" \/><\/div>\n<p>The key point here is that it shows an &#8220;Estimated Number of Rows&#8221; of 1.96 but an &#8220;Actual Number of Rows&#8221; of 385. That&#8217;s pretty far off&#8230; why? Because this statement&#8217;s plan was determined by the first execution of sp_executesql. Let&#8217;s try another execution.<\/p>\n<p>Because the query has LIKE in it, we can use wildcards. And, let&#8217;s do that! This time I&#8217;ll supply a wildcard of %e%:<\/p>\n<p>DECLARE @ExecStr NVARCHAR(4000);<br \/>\nSELECT @ExecStr = &#8216;SELECT * FROM dbo.member WHERE lastname LIKE @lastname&#8217;;<br \/>\nEXEC sp_executesql @ExecStr, N&#8217;@lastname varchar(15)&#8217;, &#8216;%e%&#8217;;<br \/>\nGO;<\/p>\n<p>And, again, the query plan looks the same&#8230; but with even thicker lines. Yes, I realize&#8230; this is not blindingly obvious:<\/p>\n<div style=\"text-align: center;\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/4\/Wildcard%20plan.png\" alt=\"\" width=\"835\" height=\"282\" \/><\/div>\n<p>And, by turning on SET STATISTICS IO ON, we can also review the [Logical] IOs performed:<\/p>\n<p>For\u00a0&#8216;Tripp&#8217;<\/p>\n<ul>\n<li>Table &#8216;member&#8217;. Scan count 1, <strong><span style=\"text-decoration: underline;\">logical reads 8<\/span><\/strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/li>\n<\/ul>\n<p>For &#8216;Anderson&#8217;<\/p>\n<ul>\n<li>Table &#8216;member&#8217;. Scan count 1, <strong><span style=\"text-decoration: underline;\">logical reads 772<\/span><\/strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/li>\n<\/ul>\n<p>For &#8216;%e%&#8217;<\/p>\n<ul>\n<li>Table &#8216;member&#8217;. Scan count 1, <strong><span style=\"text-decoration: underline;\">logical reads 10019<\/span><\/strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/li>\n<\/ul>\n<p>This table only has 144 pages. In the cases of &#8216;Anderson&#8217; and &#8216;%e%&#8217;, SQL Server would have been better off doing a table scan. But, because I used sp_executesql I forced SQL Server to do caching. Subsequent users use the plan whether it&#8217;s optimal or not.<\/p>\n<p>And, there are even more complicated scenarios than this. I plan to keep tackling these issues over the next few days and I&#8217;ll add quite a bit more to this. However, it&#8217;s late. So, I&#8217;ll dangle the carrot for now. Within the next couple of days, I&#8217;ll show a series of EXEC statements that generate both SAFE and UNSAFE plans.<\/p>\n<p>Thanks for reading!<br \/>\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my last post: Little Bobby Tables, SQL Injection and EXECUTE AS, I wanted to highlight how to avoid SQL Injection\u00a0when using EXEC. A few people\u00a0brought up the\u00a0point that I could have avoided SQL Injection by using sp_executesql. And, while sp_executesql can be used in some cases, it&#8217;s not always the most ideal from a [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26,50,59,62],"tags":[],"class_list":["post-518","post","type-post","status-publish","format-standard","hentry","category-dynamic-string-execution","category-optimizing-procedural-code","category-security","category-sp_executesql"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/518","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=518"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/518\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=518"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=518"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=518"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}