Meta-Data Only Changes Weirdness

So today’s blog is just a summary of something that I came across out there in the wild and to be honest I’m a bit confused by the whole thing, so this blog won’t be going into why this happened, because basically I don’t fully get why, ok enough mystery, lets get into it.

We have a wide table in MS SQL Server (2019), say 150 columns. We want to add a new bit column at the end of this table with a default value of 0, at first look at this I would have thought, that’s not a big deal, this is a meta-data only change but it wasn’t, the entire table got re-written, so here are some scenarios I tested.

Scenario 1: (No Page Compression on the table)

ALTER TABLE dbo.WideTable ADD Test BIT DEFAULT(0);

So the operation is instant, implying a Meta-data only change.

Scenario 2: (No Page Compression on the table)

ALTER TABLE dbo.WideTable ADD TEST BIT NOT NULL DEFAULT(0);

So the operation takes close to 30 seconds, running sp_whoisactive on the server I can see its basically reads the entire table into memory. So for me this seems to be SQL Server saying I need to read the entire table as you have a NOT NULL constraint but why does it need to check for this when its a new row.

Scenario 3: (Page Compression Enabled on the table)

ALTER TABLE dbo.WideTable ADD TEST BIT DEFAULT(0);

Again, like scenario 1, the operation is immediate, implying a meta-data only change.

Scenario 4: (Page Compression Enabled on the table)

ALTER TABLE dbo.WideTable ADD TEST BIT NOT NULL DEFAULT(0);

So the operation takes close to 70 secs to run, again using sp_whoisactive to see what’s going on, I can see that the table is being read and re-written.

So to summarize, adding the NOT NULL regardless of whether page compression is enabled or not causes the table to be fully read. If page compression is enabled on that table prior to adding the column means the entire table gets re-written.

Maybe I’m missing something here, I haven’t had my tea yet but 1. why would a “not null constraint” mean the table has to be read entirely when it a new column and 2. why would page compression on a table mean that having a not null constraint on a new column cause a full re-write, I don’t get it. Any ideas as to what I’m missing, please let me know.

So I hope you enjoyed todays blog, until next time, Slán!

Disclaimer: Never just believe everything you read in a post, test this out for yourself, we all can make mistakes! Happy coding.