in place updates causes forwarded records 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.

Recently on my system I encountered forwarded records.
Table design is like this:

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);


The insert statements ALWAYS bring HashValue AND LoadTime. I checked the query logs.
I insert a value of '9999-12-31'.

Now system performs an update on LoadTime like this:

;WITH CTE AS (
SELECT *, COALESCE(LEAD(LoadTime) OVER(PARTITION BY HashValue ORDER BY LoadTime) ,'9999-12-31') as EndTimeStamp
)
UPDATE CTE SET LoadEndTime = EndTimeStamp;


Since the LoadEndTime column is always filled, there should be no extension 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.

Insert Statement is like this:

INSERT INTO dbo.test (HashValue, LoadTime,LoadEndTime)
SELECT HASHBYTES(...), GETDATE(), '1900-01-01'


So there is already a dummy value. Even with compression the fixed and variable representation of datetime '1900-01-01 00:00:00' should be 8 bytes.
This is due to compression.

Compressed table has no more FixedVar row format, so even if you use fixed-length columns they aren't stored in Fixedvar way, no more fixed space reserved for (even NULL) values.

When you use page compression, frist ROW COMPRESSION is applied.
And here what we have:Row Compression Implementation


  It stores fixed character strings by using variable-length format by
  not storing the blank characters. Note
  
  NULL and 0 values across all data types are optimized and take no
  bytes.


Here is my repro where I use dbo.Nums, table of 1000000 natural numbers, to fill my two tables: dbo.test_comp with page compression enabled and dbo.test_no_comp with no compression:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   11,7117117117117    18519

UPDATE dbo.test_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 54949   11,1783696529459    19688


CREATE TABLE dbo.test_no_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 

insert into dbo.test_no_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE dbo.test_no_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572


UPDATE


  Even with compression the fixed and variable representation of
  datetime '1900-01-01 00:00:00' should be 8 bytes


This is completely untrue, because you use PAGE compression that creates a dictionary and substitute repeating values:


  Dictionary compression 
  
  After prefix compression is applied to every
  column individually, the second phase of page compression looks at
  all values on the page to find duplicates in any column of any row,
  even if they have  been encoded to reflect prefix usage. The
  process of  detecting duplicate values is data type–agnostic, so
  values in completely different columns could  be the same in their
  binary representation. For example, a 1-byte character is represented
  in hex as  0x54, and it would be seen as a duplicate of the 1-byte
  integer 84, which is also represented in hex  as 0x54. The dictionary
  is stored as a set of symbols, each of which corresponds to a
  duplicated value  on the data page. After the symbols and data values
  are determined, each occurrence of one of the  duplicated values is
  replaced by the symbol. SQL Server recognizes that the value actually
  stored in  the column is a symbol and not a data value by examining
  the encoding in the CD array. Values which  have been replaced by
  symbols have a CD array value of 0xc.


But I wanted point out another thing.
Did you test if your PAGE compression make sense at all in your case?
You have a heap and it's not static, i.e. you update it.


  Compression of heap pages  
  
  Pages in a heap are checked for possible compression only during 
  rebuild and shrink operations. Also, if you drop a clustered index on
  a table so that it becomes a heap, SQL Server runs compression
  analysis on any full pages. To make sure that the RowID values stay
  the same, heaps aren’t recompressed during typical data modification
  operations. Although the Page-ModCount value is maintained, SQL Server
  never tries to recompress a page based on the PageMod-Count value


So in your case even if you can achieve compression on INSERT (only if you use bulk insert with tablock), any update will only destroy your compression.

Look at my new test.

I realized that in my first test when i did INSERT without tablock, only row compression was applied, I inserted 1000000 rows and I get 19688 pages.
Now I insert with tablock into the same table:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME not NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
) 
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp with(tablock) (id,  LoadTime, LoadEndTime)
select n, getdate(), getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   0   1387


Now I have only 1387 pages.
1387 vs 19688 only because in first case page compression, even if defined, was not applied since my INSERT was without tablock

And now I update my nice compressed table:

UPDATE dbo.test_comp SET LoadEndTime = getdate();

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 990247  8,43971631205674    22442


Wow, after update I have 22422 pages vs 1387 original pages!

After this test I really NEVER will use page compression on a heap that is not static. The final table size is not much less compared with its non-compressed analog but as a bonus I have 990247 forwarded records out of 1000000