Safely add a non-null column to an existing table

Question

IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'[MySchema].[MyTable]'))
    ALTER TABLE [MySchema].[MyTable] 
        ADD [MyColumn] VARCHAR(10) NULL
GO

UPDATE [MySchema].[MyTable] 
SET [MyColumn] = 'ValueForExistingData' 
WHERE [MyColumn] IS NULL

ALTER TABLE [MySchema].[MyTable] 
    ALTER COLUMN [MyColumn] VARCHAR(10) NOT NULL

What kind of locking / transaction isolation level is required to ensure that no new rows are inserted in between the UPDATE and ALTER COLUMN statements? This is to prevent the ALTER COLUMN statement from failing because of a NULL value in MyColumn in the newly inserted row.

Is there a better alternative to GO to ensure ADD [MyColumn] completes before SET [MyColumn] ?

check this similar question https://stackoverflow.com/questions/4443262/add-column-to-table-and-then-update-it-inside-transaction — PSK, Feb 12 '20 at 04:50

Piotr Palka · Accepted Answer · 2020-02-12T15:45:23.977

1

You can use following syntax:

BEGIN TRANSACTION

ALTER TABLE [MySchema].[MyTable] 
        ADD [MyColumn] VARCHAR(10) NOT NULL CONSTRAINT MyTableDefault DEFAULT 'ValueForExistingData';

ALTER TABLE [MySchema].[MyTable] 
        DROP CONSTRAINT MyTableDefault;

COMMIT

On Enterprise edition SQL Server 2012+ both statements will be metadata only operations, so you will not have to update all data pages and the whole operation will take milliseconds.

edited Feb 12 '20 at 15:45

answered Feb 12 '20 at 05:03

Piotr Palka

3,086
1
9
17

2

It depends on edition of SQL Server whether the first one is metadata only. They need to be using Enterprise Edition for on prem version. Regardless it still does what they ask – Martin Smith Feb 12 '20 at 07:14

score 0 · Answer 2 · answered Feb 12 '20 at 05:02

You can set a Default constrain to ensure that all existing column has a value for existing data.

Then drop the default constrain to force new lines to have an entered value.

IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = N'MyColumn' AND Object_ID = Object_ID(N'[MySchema].[MyTable]'))
    ALTER TABLE [MySchema].[MyTable] 
        ADD [MyColumn] VARCHAR(10) NOT NULL DEFAULT 'ValueForExistingData';
GO

ALTER TABLE [MySchema].[MyTable] 
ALTER COLUMN [MyColumn] DROP DEFAULT;
GO

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

Safely add a non-null column to an existing table

2 Answers2