Is it possible to add a new "not null" column into a table in mssql which has a system ver-CodePudding

I have a table a_s and it has a system versioned history table a_h. Requirement is to add a new column X1 varchar(5) not null to both a_s & a_h.

Is it possible to create it like this IN MS SQL Server Studio 18?

CodePudding user response：

Adding a column to a system versioned table is no different to adding one to a non-system versioned table. When you add a column to the "main" table, the data engine will "automagically" add that column to the history table. If you also define that column with a DEFAULT CONSTRAINT and define the column as NOT NULL or state the column needs to be added WITH VALUES then rows in the history table will also be populated with that value:

See the following working example:

CREATE TABLE dbo.SomeTable (YourID int IDENTITY CONSTRAINT PK_YourID PRIMARY KEY,
                            SomeColumn varchar(10),
                            ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
                            ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
                            PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SomeTableHistory));
GO


INSERT INTO dbo.SomeTable (SomeColumn)
VALUES('abc');
GO

UPDATE dbo.SomeTable
SET SomeColumn = 'def'
WHERE YourID = 1
GO
ALTER TABLE dbo.SomeTable ADD AnotherColumn decimal(10,2) NOT NULL CONSTRAINT DF_AnotherColumn DEFAULT 0;
GO
ALTER TABLE dbo.SomeTable ADD SomeDate date NULL CONSTRAINT DF_SomeDate DEFAULT '20220101' WITH VALUES;
GO
SELECT *
FROM dbo.SomeTable;
GO
SELECT *
FROM dbo.SomeTableHistory;
GO
ALTER TABLE dbo.SomeTable SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.SomeTable;
DROP TABLE dbo.SomeTableHistory;

This gives the following results for dbo.SomeTable and dbo.SomeTableHistory respectively:

YourID      SomeColumn ValidFrom                   ValidTo                     AnotherColumn SomeDate
----------- ---------- --------------------------- --------------------------- ------------- ----------
1           def        2022-07-14 11:09:31.7890645 9999-12-31 23:59:59.9999999 0.00          2022-01-01

YourID      SomeColumn ValidFrom                   ValidTo                     AnotherColumn SomeDate
----------- ---------- --------------------------- --------------------------- ------------- ----------
1           abc        2022-07-14 11:09:31.7890645 2022-07-14 11:09:31.7890645 0.00          2022-01-01

db<>fiddle

CodePudding user response：

This solution worked: Add a constraint with default value while adding the not null column and once created, drop the constraint.

`
ALTER TABLE A_S SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE A_H ADD X1 VARCHAR(5) NOT NULL CONSTRAINT cons1 DEFAULT ' ';

ALTER TABLE A_H ADD X2 VARCHAR(5) NOT NULL CONSTRAINT cons2 DEFAULT ' ';

ALTER TABLE A_H ADD X3 VARCHAR(5) NOT NULL CONSTRAINT cons3 DEFAULT ' ';

ALTER TABLE A_S ADD X1 VARCHAR(5) NOT NULL CONSTRAINT cons4 DEFAULT ' ';

ALTER TABLE A_S ADD X2 VARCHAR(5) NOT NULL CONSTRAINT cons5 DEFAULT ' ';

ALTER TABLE A_S ADD X3 VARCHAR(5) NOT NULL CONSTRAINT cons6 DEFAULT ' ';

ALTER TABLE [SUSTAINABILITY_DB_SE].[dbo].[A_S] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[A_H]));

ALTER TABLE A_H DROP CONSTRAINT CONS1;
ALTER TABLE A_H DROP CONSTRAINT CONS2;
ALTER TABLE A_H DROP CONSTRAINT CONS3;

ALTER TABLE A_S DROP CONSTRAINT CONS4;
ALTER TABLE A_S DROP CONSTRAINT CONS5;
ALTER TABLE A_S DROP CONSTRAINT CONS6;`