Microsoft SQL Server can process an UPDATE statement using one of four possible methods. These methods are deferred, in-place, on-page delete/insert, and full delete/insert. This section discusses the rules that determine which method is used.
The decision concerning deferred update is made at compile time; the decision concerning the three nondeferred methods is made at execution time on a row-by-row basis. Since Showplan is based on compile-time information and not run-time information, the update mode it reports may be different from how the query was actually executed at run time. If the Showplan output indicates DEFERRED, the actual access plan used is always deferred. For nondeferred updates, you can use trace flag 323 to see exactly how the query was processed. Trace flag 323 is slightly different from other trace flags; it will only display output to the error log, not to the local session, and it will only write to the error log if SQL Server is started from the command prompt—for example:
sqlservr -c - dc:\mssql\data\master.dat