1

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 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.

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 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...

trincot
  • 317,000
  • 35
  • 244
  • 286
tuxmania
  • 906
  • 2
  • 9
  • 28
  • 1
    Your narrative is a bit confused given two very similarly named columns. But certainly if you're changing a column from being `NULL` to having a value, I might expect the row size to change. As I say, because you seem to be mixing up the column names in your narrative, it's hard to say if this is what's happening. – Damien_The_Unbeliever Sep 28 '17 at 07:08
  • I edited it. Point is the insert brings values for all three columns so NULL is inserted anywhere. The later update also ensures to update a valid non null datetime. So i really don't know why it creates forwarded records. – tuxmania Sep 28 '17 at 07:14
  • Am I missing something? First of all, there is no FROM in your cte. Secondly, shouldn't you filter your cte on records, where the loadtime isn't equal 9999-12-31? As far as I understood, you inserted records with the saame value you use in your coalesce, right? – Tyron78 Sep 28 '17 at 07:47

0 Answers0