HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Fastest way to add a field to a large table

Submitted by: @import:stackexchange-dba··
0
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

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.

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.