patternsqlMinor
Fastest way to add a field to a large table
Viewed 0 times
fieldwaylargefastesttableadd
Problem
I have a fairly large table (approx 400 million rows) and I need to add new column of type bit with NOT NULL constraint. My approach is to add the field without NOT NULL constraint, set the field to 0 and the alter the column to add the NOT NULL constraint.
This is a pretty slow process. Is there a quicker approach?
I am on SQL Server 2014 Enterprise Edition
This is a pretty slow process. Is there a quicker approach?
I am on SQL Server 2014 Enterprise Edition
Solution
As you are on Enterprise Edition and version >= 2012 then the information in Online non-NULL with values column add in SQL Server 2012 should apply.
Should be metadata only and, unlike your proposed solution, will not have to write anything to all rows.
Alter table yourtable
add newcolumn bit not null default 0;Should be metadata only and, unlike your proposed solution, will not have to write anything to all rows.
Code Snippets
Alter table yourtable
add newcolumn bit not null default 0;Context
StackExchange Database Administrators Q#147092, answer score: 7
Revisions (0)
No revisions yet.