title: Is dynamic SQL more performant than static SQL in SQL Server?
body: <p>I have a work mate who claims that dynamic SQL performs faster than static SQL in many situations, so I constantly see DSQL all over the place. Aside from the obvious downsides like not being able to detect errors until running it and that it's harder to read, is this accurate or not? When I asked why he was using DSQL all the time, he said:</p>
  
  <blockquote>
    <p>Static is preferred when it is not going to prevent cache reuse and dynamic is preferred when static will prevent cache reuse and reuse is desirable.</p>
  </blockquote>
  
  <p>I asked why static SQL would prevent cache reuse and he said:</p>
  
  <blockquote>
    <p>Apparently, when variables are passed to statement predicates it may prevent cache reuse of that execution plan, where DSQL will allow cache reuse in a stored procedure.</p>
  </blockquote>
  
  <p>So, for example:</p>
  
  <pre><code>select * from mytable where myvar = @myvar
  </code></pre>
  
  <p>I'm not an expert in SQL Server execution plans, but this seems irrational to me. Why would the engine keep stats in a DSQL statement in a stored procedure, but not a static SQL statement?</p>