title: SQL Server - Variable substitution slow
body: <p>I have a table "my_table" with 20 million entries. The id-column is indexed. If i do the following query it takes less than 1 sek.</p>
  
  <pre><code>SELECT * FROM my_table WHERE id='asdf'
  </code></pre>
  
  <p>If I do the same query but with a temporary variable in the where clause.</p>
  
  <pre><code>declare @ID nvarchar(4)
  set @ID = N'asdf'
  SELECT * FROM my_table WHERE id=@ID
  </code></pre>
  
  <p>it takes ~14 sek. </p>
  
  <p>Execution plan with temporary variable:</p>
  
  <pre><code>select &lt;- Clustered Index Scan
  </code></pre>
  
  <p>Execution plan with constant:</p>
  
  <pre><code>select &lt;- Nested Loops &lt;- Index Seek (Non clustered)
                         &lt;- Key Lookup (Clustered)
  </code></pre>
  
  <p><strong>First question:</strong>
  Why is the first query taking that much longer to execute?<br>
  SQL Server Profiler tells me that the first query does ~800000 reads (cpu 5258) while the second does 25 reads (cpu 0).</p>
  
  <p><strong>Second question:</strong>
  If I apply OPTION(RECOMPILE) to the first query, execution will run fast and the execution plan is like the second but I don't understand why.<br>
  Is the variable evaluated for every entry in the seek?</p>