I can add a column this way:
ALTER TABLE MyTable
ADD MyColumn BIT NOT NULL
CONSTRAINT MyConstraint
DEFAULT 0
Now, instead of the constant 0
I want to put a different value depending on my other columns in the MyTable
. I.e. it is a one time computation during the column addition. How could I do that?
Also, I do not want my column to have a default value.
E.g. I have another BIT
column called MyOldColumn
and I want to just copy its value into the MyColumn
.
CodePudding user response:
Looks like you just want to add the column, then run an update using some calculation.
ALTER TABLE MyTable ADD MyColumn BIT;
UPDATE MyTable
SET MyColumn = SomeCalculation;
ALTER TABLE MyTable ALTER COLUMN MyColumn BIT NOT NULL;
Why set the column to null
first then not null
? Because it will be more performant. Setting it to null
first means it's a meta-data only operation, and will not change the data pages. The final ALTER
will also not change any pages (although it will read them all), because none of them are null.
Whereas if you set it to not null
first then all pages get changed, and then changed again.