3

I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table metadata, as we can learn there. It works perfectly, SELECTs return a default value. After some time I decided to change the default to a different value. And it looks like we can't achieve it by changing the default. I mean, we can drop old default and create new one, but all SELECTSs would show old default value. I'd try to avoid massive UPDATEs because they are long and painful. Is it possible?

--DROP TABLE #t

CREATE TABLE #t
(
  c1 int not null
)

INSERT INTO #t VALUES (1)

ALTER TABLE #t ADD c2 int not null CONSTRAINT t#c2def default -1
ALTER TABLE #t DROP CONSTRAINT t#c2def

SELECT * FROM #t
-- c2 = -1
ALTER TABLE #t ADD CONSTRAINT t#c2def DEFAULT -2 FOR c2

SELECT * FROM #t
-- still c2 = -1

And more generally, do we have more details on how this feature works? I'd expect that existing non-updated pages should show a new default. That's how I understand 'the default value is stored only in the metadata of the table and the value is looked up, as needed'. Unfortunately, it doesn't work like this. All rows show old default value. I'm checking it on SQL Server 2014.

Improve this question
2
  • 1
    So you've correctly identified how the new default actually functions. So does that mean your question is how best to update a column for a large number of rows? It might also be helpful to define what a "very big table" is and what your definition of "long and painful" measures up to.
    – LowlyDBA - John M
    Mar 19, 2019 at 16:41
  • @LowlyDBA, I wonder if we can use the magic of 'add a non-null column with default instantly' to change the default (no, we can't). Updating a huge amount of rows is a known business, that's what I'm trying to avoid. The big table in my case is 300M rows, 10GB. 'Long and painful' actually means we have to design and deploy process to populate a copy of the table and then rename/replace. In my case update it in-place is not an option.
    – Alsin
    Mar 19, 2019 at 17:08

1 Answer 1

Reset to default
1

I found my answers here. The default value is not the same as default constraint. It is created when we create first default constraint and can't be changed.

So, to answer my question - no, it is not possible. We can't change the default value for a column. This magic works only once. After this, we should do regular DML to change values in rows.

Improve this answer
8
  • 1
    For the record, a default value automatically creates a default constraint that is named by the system, so they are indeed the same thing.
    – LowlyDBA - John M
    Mar 19, 2019 at 17:02
  • @LowlyDBA, per Rusanu article, "Is worth saying that the new SQL Server 2012 default column value is not the same as the default value constraint."
    – Alsin
    Mar 19, 2019 at 17:18
  • 1
    Yes, the article says that and is wrong, hence why I commented to clear up that misinformation. There are some differences of how it functions when you define it initially and if you were to change it, but conceptually it is still a constraint. See the documentation for clarity.
    – LowlyDBA - John M
    Mar 19, 2019 at 17:20
  • @LowlyDBA, Rusanu's reasoning sounds very convincing for me. The default value can be assigned only once and applies to existing rows, Default constraint can be changed and applies to new rows. From this point of view, they are not the same thing. Why do you think Rusanu is wrong?
    – Alsin
    Mar 19, 2019 at 17:25
  • The feature update from 2012 changes the behavior of online, runtime constant defaults, but 1) does not apply to all scenarios and 2) still results in the creation of a system named default constraint object. I believe he chose unclear wording by accident in all likelihood, but they are still both "default value constraints" which I think is an important distinction.
    – LowlyDBA - John M
    Mar 19, 2019 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.