title: Index seek used in query but index scan in stored procedure
body: <p>I'm experiencing some strange behaviour in SQL Server 2008 R2.</p>
  
  <p>I have the following query:</p>
  
  <pre><code>UPDATE  TableToUpdate
  SET     ColumnToUpdate = @ColumnValue
  WHERE   ColumnA IN
          (
              SELECT  ColumnA
              FROM    SubQ1Table
              WHERE   ColumnToUpdateReference = @ColumnValue
          )
  and     ColumnB in
          (
              SELECT  ColumnB
              FROM    SubQ2Table
              WHERE   ColumnToUpdateReference = @ColumnValue
          )
  </code></pre>
  
  <p>When I run the query manually and declare the variable @ColumnValue as follows:</p>
  
  <pre><code>DECLARE @ColumnValue INT = 123;
  </code></pre>
  
  <p>It runs in seconds and does an Index Seek on the index covering columns ColumnA, ColumnB and ColumnToUpdate on the TableToUpdate table.</p>
  
  <p>When I create a stored procedure using the exact query except the parameter is passed in.. eg:</p>
  
  <pre><code>EXEC sp_Query 123
  </code></pre>
  
  <p>An Index Scan is used on the same index and takes around 30 seconds to complete.</p>
  
  <p>I've looked at both query plans which are different. Reading from left to right the first difference is the stored procedure seems to do a Nested Loops (Inner Join) Whereas the direct query does a Stream Aggregate (Aggregate).</p>
  
  <p>Why would calling this SQL through a stored-procedure make a difference? If you need me to provide any more information let me know.</p>
  
  <p>Thanks in advance,</p>
  
  <p>Tom.</p>