Adding new column with DEFAULT

Question

Post reply

Adding new column with DEFAULT

Thomas Franz

SSCarpal Tunnel

Points: 4040
More actions
September 7, 2026 at 12:00 am

#4962014

Comments posted to this topic are about the item Adding new column with DEFAULT
God is real, unless declared integer.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80222 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

MMartin1 One Orange Chip Points: 27890 More actions · Answer 2

The key is knowing that adding a column default constraint does not alter existing data.

----------------------------------------------------

Thomas Franz SSCarpal Tunnel Points: 4040 More actions · Answer 3

yes and no.

adding a default constraint to an existing column doesn't change anything
adding a new column and a DEFAULT CONSTRAINT with a single statement, doesn't change the pages of the table (just the metadata), so the table is not completely rewritten (important on large tables).
when the new column does not allow NULL, all rows are initialized with the DEFAULT value (still just in the meta data)
when the new column allows NULL, the existing rows does not become initialized and keep NULL as value, only new added rows use the DEFAULT
adding a new NOT NULL column without a DEFAULT is not possible / allowed

God is real, unless declared integer.

MMartin1 One Orange Chip Points: 27890 More actions · Answer 4

Good point to note

when the new column does not allow NULL, all rows are initialized with the DEFAULT value (still just in the meta data)

In the example the column was NULLable

----------------------------------------------------

Uwe Ricken Hall of Fame Points: 3270 More actions · Answer 5

Hi Thomas,

"adding a new column and a DEFAULT CONSTRAINT with a single statement, doesn't change the pages of the table (just the metadata), so the table is not completely rewritten (important on large tables)."

Be careful with this - only valid in Enterprise Edition. Standard Edition will add the DEFAULT as data to the table.

Just ran into this with a customer because he tested on DEV (EE) and was astonished about the bad performance in PROD (SE) 🙂

Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)

Thomas Franz SSCarpal Tunnel Points: 4040 More actions · Answer 6

@Uwe thanks, I could verify / test it with an Express edition where the data was written to the #temp table and the following query showed no default (while on DEV / Enterprise it will show the init value from the ALTER TABLE #temp ADD x BIT NOT NULL DEFAULT 1)

SELECT
    c.name,
    i.has_default,
    i.default_value
FROM tempdb.sys.partitions p
JOIN tempdb.sys.system_internals_partition_columns i
    ON p.partition_id = i.partition_id
JOIN tempdb.sys.columns c
    ON c.object_id = p.object_id
   AND c.column_id = i.partition_column_id
WHERE p.object_id = OBJECT_ID('tempdb..#tmp')
  AND p.index_id IN (0,1);

God is real, unless declared integer.