title: in-place update leads to forwarded records
body: <p>I am well aware what a forwarded record within a heap is.
  Since I want to keep forwarded records at 0, we decided to update only on columns that could not be extended.</p>
  
  <p>Recently on my system I encountered forwarded records.
  Table design is like this:</p>
  
  <pre><code>CREATE TABLE dbo.test (
   HashValue BINARY(16) NOT NULL,
   LoadTime DATETIME NOT NULL,
   LoadEndTime DATETIME NULL,
  [other columns that never get updates]
  ) WITH(DATA_COMPRESSION=PAGE);
  </code></pre>
  
  <p>The insert statements ALWAYS brings all the columns, so none is left NULL. I checked the query logs.
  I insert a value of '9999-12-31' for the LoadEndTime.</p>
  
  <p>Now system performs an update on LoadTime like this.</p>
  
  <pre><code>;WITH CTE AS (
  SELECT *, COALESCE(LEAD(LoadTime) OVER(PARTITION BY HashValue ORDER BY LoadTime) ,'9999-12-31') as EndTimeStamp
  )
  UPDATE CTE SET LoadEndTime = EndTimeStamp;
  </code></pre>
  
  <p>since the LoadEntTime column is always filled there should be no extention of that column within the row when the update is executed. It should be an in place update. Still i get forwarded records always after that process... It doesn't make sense to me...</p>