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.