title: SQL Server UPDATE statement not updating rows matching constraints
body: <p>If I read this question I would immediately think "some idiot asking for help before doing any research himself".  While it remains to be seen whether or not the "idiot" designation is correct, I can assure you this isn't my first rodeo and I've beat my head against this wall for 40-80 hours.</p>
  
  <p><strong>Short Story:</strong> My system executes 1 update statement four times passing in four different parameter values.  Based on the constraints each execution should modify approximately 50,000 rows.  However while 2 of the executions modify the expected number of rows, the other two only update 201 rows.</p>
  
  <p><strong>Disclaimer:</strong>
  In an effort to convince you I'm not a noob, who is updating one Database and then wondering why the records in a different DB aren't updated. </p>
  
  <ul>
  <li>I'm a programmer with 15+ years experience, specializing in systems in which batch processing performance is critical</li>
  <li>My level of expertise with SQL Server is way below the typical DBA, but considerably higher than the typical developer</li>
  <li>As I mentioned above I have spent 40-80 hours researching this issue</li>
  <li>I am out of ideas</li>
  </ul>
  
  <p><strong>Long Story:</strong></p>
  
  <p>The table in question looks something like:</p>
  
  <ul>
  <li>MyPkCol - BIGINT - Not involved in UPDATE statement. Only column in clustered index</li>
  <li>MyForeignKeyCol1 - BIGINT - Pointer to another table which is cleared by Update </li>
  <li>MyIntCol - INT - Basic data which is cleared by Update</li>
  <li>MyForeignKeyCol2 - BIGINT - Pointer to another table which is cleared by Update and is the only constraint</li>
  </ul>
  
  <p>There is a non-clustered index which only contains <code>MyForeignKeyCol1</code> (no includes).</p>
  
  <p>There is another non-clustered index which only contains <code>MyForeignKeyCol2</code> (no includes).</p>
  
  <p>There is a foreign key for <code>MyForeignKeyCol1</code> and another on <code>MyForeignKeyCol2</code>.</p>
  
  <p>The update statement is:</p>
  
  <pre><code>UPDATE T1 
  SET MyForeignKeyCol1 = NULL, 
      MyForeignKeyCol2 = NULL, 
      MyIntCol = NULL 
  WHERE MyForeignKeyCol2 = ?
  </code></pre>
  
  <p>The problem is highly inconsistent. I've ran the process 200-300 times and the problem has only occurred 3 times to date.</p>
  
  <p>In the most recent occurrence, the table in question contained about 200,000 records. 50,000 contained a value of 123 in <code>MyForeignKeyCol2</code>, 50,000 contained a value of 345 in <code>MyForeignKeyCol2</code>, 50,000 contained a value of 567 in <code>MyForeignKeyCol2</code>, and 50,000 contained a value of 789 in <code>MyForeignKeyCol2</code>.</p>
  
  <p>The process executes the Update statement 4 times passing in parameters of 123, 345, 567 and 789. Each of the 4 Updates is performed in a separate transaction.  The database driver reports that the statements modified the following number of records:</p>
  
  <ul>
  <li>50,000</li>
  <li>201</li>
  <li>201</li>
  <li>50,000</li>
  </ul>
  
  <p>The state of the DB shows that 49,799 records remain with <code>MyForeignKeyCol2=345</code> and another 49,799 records remain with <code>MyForeignKeyCol2=567</code>.</p>
  
  <p>The database contains no triggers.</p>
  
  <p>One of my first theories is that at the moment the UPDATE was executed the <code>MyForeignKeyCol2</code> column on the skipped records contained some value other than 345 or 567, then later after the UPDATE something is populating the column with those values. After many hours of research I'm 99.99% sure that is not the case.</p>
  
  <p>Could this type of behavior be caused by something like a corrupt Index or out of date statistics?</p>
  
  <p>In my experience the DB is the most reliable part of any system I'm involved in, and thus the last thing I ever suspect, but that is the only theory that I have left.</p>
  
  <p>Thanks in advance!</p>