Add a column with a value computed from other columns in SQL without using the default value

manymanymore 0
Viewed 33

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.

default calculated-columns tsql constraints sql
1 Answers

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.

Charlieface0
Related