It's interesting behaviour. And if we had to guess, we'd probably think SQL creates internally metadata substitution (Default Substitution), when we create Default Constraint. But this Default Substitution is not bound to Default Constraint anymore and acts independently and stays in place until column itself is dropped or all rows are updated explicitly. So the purpose of Default Constraint - supply values for new rows. And purpose of Default Substitution - supply values for rows where data is absent.

Funny.

I tried to confuse SQL and dropped initial Default Constraint, then created another one with different default value. But it still selects values taken from original Default, as if they all were populated into rows and sit there. So it seems that this new kind of object (Default Substitution) is created only during column creation process when it's NOT NULL and Default Constraint is supplied at the same time. And this Default Substitution stays in place (forever) to represent data that is implied to be in pages.

It's really something new. Thank you for revealing.

Actually it recollects calculated fields of the kind ISNULL(ColumnValue, OriginalDefaultValue).