Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

When I execute this test script, the ALTER with UPDATE takes 6 seconds which is understandable.

However, the ALTER with the DEFAULT NOT NULL executes instantaneously even on a much larger table. Is there any explanation on why this is instantaneous? On the physical disk, data still needs to be written to all rows right?

I tried looking at SET STATISTICS IO ON and the Query plan, however those dont seem to be available for DDL operations.

share|improve this question
up vote 18 down vote accepted

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.system_internals_partition_columns, which prevents all the values from having to be written (until they are changed). Note that this doesn't work for all data types and requires Enterprise Edition.

Remus Rusanu explains this in more detail here:

Also, for an ALTER at least, we still can't show you a plan because SQL Server doesn't produce one, but to see I/O, you can use SQL Sentry Plan Explorer.* This screen shot shows adding a column, c5, "online" as described above, and then another column, c6, "offline" because LOB types are not supported. You can see the I/O is mostly expressed as reads rather than writes, but what's more telling is the (invalid!) UPDATE associated with the offline alter.

I/O for online vs. offline alter

If you don't have Enterprise Edition, both statements will have the secondary UPDATE attached (and the associated reads). (And if you use the free version of Plan Explorer, which doesn't get the full query call stack, you won't see the above - you will just see an empty statement tree. A paid version is required to see the full query call stack.)

Note that SQL Server will produce an estimated plan, but it's not very useful. At all. And the estimated plan for an online alter is identical to the estimated plan for an offline alter.

Plan diagram for online alter

*Disclaimer: I work for SQL Sentry.

share|improve this answer
2  
+1: esp. for "Enterprise Edition". I have always wondered why it didn't work at some of my customer sites ... – RBarryYoung Mar 1 at 20:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.