title: SQLServer - MERGE with condition is slow, even an "always false" condition
body: <p>I want to execute the MERGE statement conditionally, so it won't try to match the entire target-table.
  My original statement was kinda like this:</p>
  
  <pre><code>MERGE [target_table] USING [table_source]
  ON (([target_table].[ID] = [table_source].[ID]) AND (condition))
  WHEN MATCHED THEN UPDATE
  SET [table_source].[_strField1] = [table_source].[_strField2];
  </code></pre>
  
  <p>Note: assume '_strField' to be typed as nvarchar(4000), and 'condition' to be something like [target_table].[_strField8] = 'sometext'.</p>
  
  <p>But then I've encountered the following warning in the <a href="http://technet.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow">documentation</a> that dictates <code>"...Do not attempt to improve query performance by filtering out rows in the target table in the ON clause"</code>.</p>
  
  <p>So my original query was altered to the following one</p>
  
  <pre><code>MERGE [target_table] USING [table_source]
  ON (([target_table].[ID] = [table_source].[ID]))
  WHEN MATCHED AND (condition) 
  THEN UPDATE
  SET [table_source].[_strField] = [table_source].[_strField];
  </code></pre>
  
  <p>The problem is, that the query now takes a lot more time. Even changing the condition to be "always false", such as <strong>1 = 2</strong> doesn't help at all. On the other hand, setting different fields, such as </p>
  
  <pre><code>SET [table_source].[_intField] = [table_source].[_intField];
  </code></pre>
  
  <p>or any other types other than two nvarchar(4000)s causes the statement to be executed much faster.</p>
  
  <p>To conclude, the things I don't understand are:</p>
  
  <ul>
  <li>If the data-setting of nvarchar(4000) is the longer process, why setting the condition to be "1 = 2" doesn't speed up the execution time?</li>
  <li>If the "row-matching" is the longer process, why setting INT fields does speed up the execution time?</li>
  </ul>