Dlaczego dodawanie kolumny NOT NULL z domyślnym ograniczeniem jest natychmiastowe?

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

Kiedy wykonać ten skrypt testowej ALTERz UPDATEtrwa 6 sekund, co jest zrozumiałe.

Jednak ALTERz DEFAULT NOT NULLwykonuje natychmiast, nawet na znacznie większym stole. Czy jest jakieś wyjaśnienie, dlaczego jest to natychmiastowe? Na dysku fizycznym dane nadal muszą być zapisywane we wszystkich wierszach, prawda?

Próbowałem spojrzeć na SET STATISTICS IO ONplan kwerendy, jednak te wydają się być niedostępne dla operacji DDL.

Tak, dodanie kolumny z wartością NOT NULL i wartością domyślną tak naprawdę nie zapisuje wartości do wszystkich wierszy w momencie zmiany, więc nie jest to już operacja na rozmiar danych. Po wybraniu z tabeli kolumny są faktycznie zmaterializowane z sys.system_internals_partition_columns , co zapobiega zapisywaniu wszystkich wartości (aż do ich zmiany). Pamiętaj, że nie działa to dla wszystkich typów danych i wymaga wersji Enterprise.

Remus Rusanu wyjaśnia to bardziej szczegółowo tutaj:

• Online nie-NULL z kolumną wartości dodaną w SQL Server 2012

Co ALTERnajmniej, nadal nie możemy pokazać ci planu, ponieważ SQL Server go nie produkuje, ale aby zobaczyć I / O, możesz użyć SQL Sentry Plan Explorer . * Ten zrzut ekranu pokazuje dodanie kolumny, c5 , „online” jak opisano powyżej, a następnie kolejna kolumna, c6, „offline”, ponieważ typy LOB nie są obsługiwane. Widać, że operacje we / wy są w większości wyrażane jako odczyty, a nie zapisy, ale bardziej wymowne jest (nieprawidłowe!) UPDATESkojarzone z modyfikacją offline.

Jeśli nie masz wersji Enterprise Edition, do obu instrukcji zostanie UPDATEdołączony plik pomocniczy (i powiązane odczyty). (A jeśli korzystasz z bezpłatnej wersji Eksploratora Planów, która nie otrzymuje pełnego stosu wywołań zapytań, nie zobaczysz powyższego - zobaczysz tylko puste drzewo instrukcji. Aby zobaczyć pełne zapytanie, wymagana jest płatna wersja. stos wywołań.)

Zauważ, że SQL Server wygeneruje szacunkowy plan, ale nie jest to bardzo przydatne. W ogóle. Szacowany plan zmiany online jest identyczny z planowanym zmianą offline.

* _{Zastrzeżenie: Pracuję dla SQL Sentry.}