title: SQL optimization - execution plan changes based on constraint value - Why?
body: <p>I've got a table <strong>ItemValue</strong> full of data on a SQL 2005 Server running in 2000 compatibility mode that looks something like (it's a User-Defined values table):</p>
  
  <pre><code>ID    ItemCode     FieldID   Value
  --    ----------   -------   ------
   1    abc123             1   D
   2    abc123             2   287.23
   4    xyz789             1   A
   5    xyz789             2   3782.23
   6    xyz789             3   23
   7    mno456             1   W
   9    mno456             3   45
                                   ... and so on.
  </code></pre>
  
  <p><em>FieldID</em> comes from the <strong>ItemField</strong> table:</p>
  
  <pre><code>ID   FieldNumber   DataFormatID   Description   ...
  --   -----------   ------------   -----------
   1             1              1   Weight class
   2             2              4   Cost
   3             3              3   Another made up description
   .             .              x   xxx
   .             .              x   xxx
   .             .              x   xxx
   x             91  (we have 91 user-defined fields)
  </code></pre>
  
  <p>Because I can't PIVOT in 2000 mode, we're stuck building an ugly query using CASEs and GROUP BY to get the data to look how it should for some legacy apps, which is:</p>
  
  <pre><code>ItemNumber   Field1   Field2    Field3 .... Field51
  ----------   ------   -------   ------
      abc123   D        287.23    NULL
      xyz789   A        3782.23   23
      mno456   W        NULL      45
  </code></pre>
  
  <p>You can see we only need this table to show values up to the 51st UDF. Here's the query:</p>
  
  <pre><code>SELECT
      iv.ItemNumber,
      ,MAX(CASE WHEN f.FieldNumber = 1 THEN iv.[Value] ELSE NULL END) [Field1]
      ,MAX(CASE WHEN f.FieldNumber = 2 THEN iv.[Value] ELSE NULL END) [Field2]
      ,MAX(CASE WHEN f.FieldNumber = 3 THEN iv.[Value] ELSE NULL END) [Field3]
          ...
      ,MAX(CASE WHEN f.FieldNumber = 51 THEN iv.[Value] ELSE NULL END) [Field51]
  FROM ItemField f
  LEFT JOIN ItemValue iv ON f.ID = iv.FieldID
  WHERE f.FieldNumber &lt;= 51
  GROUP BY iv.ItemNumber
  </code></pre>
  
  <p>When the <em>FieldNumber</em> constraint is &lt;= 51, the execute plan goes something like:</p>
  
  <p><code>SELECT &lt;== Computer Scalar &lt;== Stream Aggregate &lt;== Sort (Cost: 70%) &lt;== Hash Match &lt;== (Clustered Index Seek &amp;&amp; Table Scan)</code></p>
  
  <p>and it's fast! I can pull back 100,000+ records in about a second, which suits our needs.</p>
  
  <p>However, if we had more UDFs and I change the constraint to anything above <strong>66</strong> (yes, I tested them one by one) or if I remove it completely, I lose the Sort in the Execution plan, and it gets replaced with a whole bunch of Parallelism blocks that gather, repartition, and distribute streams, and the entire thing is slow (30 seconds for even just 1 record).</p>
  
  <p><em>FieldNumber</em> has a clustered, unique index, and is part of composite primary key with the <em>ID</em> column (non-clustered index) in the <strong>ItemField</strong> table. The <strong>ItemValue</strong> table's <em>ID</em> and <em>ItemNumber</em> columns make a PK, and there is an extra non-clustered index on the <em>ItemNumber</em> column.</p>
  
  <p><strong>What is the reasoning behind this? Why does changing my simple integer constraint change the entire execution plan?</strong></p>
  
  <p>And if you're up to it... <strong>what would you do differently?</strong> There's a SQL upgrade planned for a couple months from now but I need to get this problem fixed before that.</p>