Alter Table Script

-- Set all existing NULL value to 0
UPDATE t SET t.Col01 = 0 FROM tbTable01 t WHERE t.Col01 IS NULL

-- Make column NOT NULL
ALTER TABLE tbTable01 ALTER COLUMN Col01 TINYINT NOT NULL;

-- ADD Default Constraint of 0
ALTER TABLE tbTable01 ADD CONSTRAINT [DF_tbTable01_Col01] DEFAULT ((0)) FOR Col01
-- Add column NOT NULL
-- ADD Default Constraint of 0
ALTER TABLE tbTable01 ADD Col01 TINYINT NOT NULL CONSTRAINT [DF_tbTable01_Col01] DEFAULT ((0));

Create Table Script

CREATE TABLE tbTable01(
[Col01] [TINYINT] NOT NULL CONSTRAINT DF_tbTable01_Col01 DEFAULT 0,
[Col02] [DATETIME] NOT NULL CONSTRAINT [DF_tbTable01_Col02] DEFAULT (GETDATE())
[Col03] [DATETIME2] NOT NULL CONSTRAINT [DF_tbTable01_Col03] DEFAULT (SYSDATETIME())

Last modified: December 11, 2019

Author

Comments

Write a Reply or Comment