Default Column Storage

Paul Randal explains how default column values are stored:

And selecting the initial 10 rows can be demonstrated to return the 3rd column using the initial default set in step 3. (It makes no difference if any rows are added between steps 3 and 4.)

This means that there *must* be two default values stored when a new column is added: one for the set of already-existing rows that don’t have the new column and one for any new rows. Initially these two default values will be the same, but the one for new rows can change (e.g. in steps 4 and 5 above) with breaking the old rows. This works because after the new column is added (step 3 above), it’s impossible to add any more rows that *don’t* have the new column.

And this is exactly how it works. Let’s investigate!

In typical Paul Randal fashion, this is both a look at internals and an interesting explanation.

Related Posts

Cleaning Up Foreign Keys

Adrian Buckman looks at how to clean up untrusted foreign key constraints: SQL Server tracks untrusted Foreign keys in sys.Foreign keys with a column called is_not_trusted, there may be a number of reasons why a Foreign key may have become untrusted below are a couple of examples: Foreign key was disabled using the ‘NOCHECK’ option then re-enabled using […]

Read More

More R Services Internals Spelunking

Niels Berglund continues his series on R Services internals: What happens is that straight after the AuthenticateConnection you will hit the WriteAsync breakpoint twice, the same way as we see in Code Snippet 4. The first hit at WriteAsync sort of makes sense, as it ties up with the call-stack we see in Code Snippet 5. But what about the second WriteAsync (the one that […]

Read More


April 2017
« Mar May »