title: SQL Server 2008 UPDATE Statement WHERE clause precedence
body: <p>I wrote the following query:</p>
  
  <pre><code>UPDATE king_in
  SET IN_PNSN_ALL_TP_CNTRCT_CD = IN_PNSN_ALL_TP_CNTRCT_CD + '3'
  WHERE COALESCE(IN_PNSN_ALL_TP_CNTRCT_TX, '') &lt;&gt; ''
          AND CHARINDEX('3', IN_PNSN_ALL_TP_CNTRCT_CD) = 0
  </code></pre>
  
  <p>It checks to see if a field has a value in it and if it does it puts a 3 in a corresponding field if there isn't a 3 already in it.  When I ran it, I got a string or binary data will be truncated error.  The field is a VARCHAR(3) and there are rows in the table that already have 3 characters in them but the rows that I was actually doing the updating on via the WHERE filter had a MAX LEN of 2 so I was completely baffled as to why SQL Server was throwing me the truncation error.  So I changed my UPDATE statement to:</p>
  
  <pre><code>UPDATE king_in
  SET IN_PNSN_ALL_TP_CNTRCT_CD = k.IN_PNSN_ALL_TP_CNTRCT_CD + '3'
  FROM king_in k
          INNER JOIN
                       (
                          SELECT ki.row_key,
                                  in_sqnc_nb
                          FROM king_in ki
                                  INNER JOIN King_Ma km
                                      ON ki.Row_Key = km.Row_Key
                                  INNER JOIN King_Recs kr
                                      ON km.doc_loc_nb = kr.ACK_ID
                          WHERE CHARINDEX('3', IN_PNSN_ALL_TP_CNTRCT_CD) = 0
                                  AND COALESCE(IN_PNSN_ALL_TP_CNTRCT_TX, '') &lt;&gt; ''
                        ) a
              ON k.Row_Key = a.Row_Key 
                  AND k.in_sqnc_nb = a.insr_sqnc_nb
  </code></pre>
  
  <p>and it works fine without error.  </p>
  
  <p>So it appears based on this that when doing an UPDATE statement without a FROM clause that SQL Server internally goes through and runs the SET statement before it filters the records based on the WHERE clause.  Thats why I was getting the truncation error, because even though the records I wanted to update were less than 3 characters, there were rows in the table that had 3 characters in that field and when it couldn't add a '3' to the end of one of those rows, it threw the error.</p>
  
  <p>So after all of that, I've got a handful of questions.</p>
  
  <blockquote>
    <p>1) Why?  Is there a specific DBMS reason that SQL Server wouldn't filter the result set before applying the SET statement?</p>
    
    <p>2) Is this just a known thing about SQL that I never learned along the way?</p>
    
    <p>3) Is there a setting in SQL Server to change this behavior?</p>
  </blockquote>
  
  <p>Thanks in advance.</p>