ALTER TABLE

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Add a column to a disposable table and inspect its populated default. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Notes (NoteID int NOT NULL PRIMARY KEY);
INSERT INTO #Notes VALUES (1);
ALTER TABLE #Notes ADD IsActive bit NOT NULL DEFAULT (1);
SELECT NoteID, IsActive FROM #Notes;
DROP TABLE #Notes;

Read it step by step

1. CREATE TABLE and INSERT establish a row in a temporary table.
2. ALTER TABLE adds a non-NULL bit column with a default; SQL Server fills the existing row.
3. The final SELECT checks the new schema and value before DROP.

Check the result

The real dbo tables are untouched.

Practice

Add a non-NULL Quantity column with DEFAULT 1 to a temp table containing NoteID 2.

CREATE TABLE #Notes (NoteID int NOT NULL PRIMARY KEY);
INSERT INTO #Notes VALUES (2);
ALTER TABLE #Notes ADD Quantity int NOT NULL DEFAULT (1);
SELECT NoteID, Quantity FROM #Notes;
DROP TABLE #Notes;