SQL Server – How to Replace Default Value on a Table

default valuesql server

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.

Best Answer

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.

Related Solutions

SQL Server – Understanding DEFAULT Constraint Value Addition

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. For example for the following table definition

 CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL)

The first three insert statements will insert NULL mark for ColB. Only the last one will have ColB value assigned.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');

Whereas for the following table definition with DEFAULT constraint.

CREATE TABLE Test(
    ColA  [nvarchar](50) NOT NULL,
    COLB [nvarchar](50) NULL DEFAULT 'x')

Only the second statement below will insert NULL mark for ColB. The first and the third statements will assign 'x' as the DEFAULT value.

 insert into Test(ColA, ColB) values ('a', DEFAULT);
 insert into Test(ColA, ColB) values ('b', NULL);
 insert into Test(ColA) values ('c');
 insert into Test(ColA, ColB) values ('d', 'e');

Why is add timestamp column with default not null so slow in Oracle 11g

This is actually improved in Oracle 12c.

From Oracle Magazine's Ask Tom column: http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html

In Oracle Database 11g you were able to perform a fast add of a column to a table if it had a default value and was defined as NOT NULL. (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you attempted to add a column with a default value and that column permitted null values, the ADD COLUMN operation could take a significant amount of time, generate a large amount of undo and redo, and lock the entire table for the duration of the operation. In Oracle Database 12c, that time, volume, and locking are no longer part of the process.

I know this doesn't help if you're stuck on 11gR2, since you probably want to get that column added before taking an outage to upgrade the DB.

As to why it takes so long only when the data type is TIMESTAMP, I'm at a loss. Maybe since the TIMESTAMP data type is a few more bytes to store than DATE, or than VARCHAR2 for empty strings, and those extra bytes are causing a cascade of row migrations. To test that theory, try adding a fixed length CHAR column (in a non-Prod environment): alter table my_table add my_char_col CHAR(200) default 'hello' not null; I expect this would not be instantaneous due to row migrations.

Your best bet on 11gR2 might be either to

Take an outage at an appropriate time that is ok with your stakeholders to add this column

Use DBMS_REDEFINITION to add the column as part of an online redefinition. This should be non-blocking, and even though it may take hours, the users will not even notice it happening (no locking). If you have a lot of indexes, this becomes a little more difficult. If you have foreign keys, this becomes even more difficult. If you have stored procs or views that reference this table, be sure to recompile them after you FINISH the redef. This will have a side-benefit that your table will be reorganized (i.e. defragmented).

Maybe add the column without the NOT NULL constraint. When that finishes, then alter the column to add the NOT NULL constraint. My intuition is that you'll still get locking somewhere in the process, so no promises. Maybe just a crazy idea.

If you want to try the Online Redefinition, then you might find these links helpful:

Best Answer

Related Solutions

SQL Server – Understanding DEFAULT Constraint Value Addition

Why is add timestamp column with default not null so slow in Oracle 11g

Related Question