Szybko zmień kolumnę NULL na NOT NULL

Mam tabelę z milionami wierszy i kolumnę, która dopuszcza wartości NULL. Jednak żaden wiersz nie ma obecnie wartości NULL dla tej kolumny (mogę to dość szybko zweryfikować za pomocą zapytania). Jednak gdy wykonam polecenie

ALTER TABLE MyTable ALTER COLUMN MyColumn BIGINT NOT NULL;

zapytanie trwa wiecznie relatywnie rzecz biorąc. W rzeczywistości zajmuje to od 10 do 20 minut, ponad dwa razy dłużej niż dodanie ograniczenia sprawdzania. Czy istnieje sposób na natychmiastową aktualizację metadanych tabeli dla tej kolumny, zwłaszcza że wiem, że żaden wiersz nie ma wartości NULL dla tej kolumny?

Odpowiedź @ ypercube radzi sobie z tym częściowo, ponieważ zmienia się tylko metadane.

Dodanie ograniczenia NOCHECKoznacza, że ​​żadne wiersze nie będą musiały być czytane, aby je zweryfikować, a jeśli zaczynasz od pozycji, w której kolumna nie zawiera NULLwartości (i jeśli wiesz, że żaden nie zostanie dodany między sprawdzaniem a dodawaniem ograniczenia), ponieważ ograniczenie uniemożliwia tworzenie NULLwartości z przyszłości INSERTlub UPDATEoperacji, to zadziała.

Dodanie ograniczenia może jednak mieć wpływ na współbieżne transakcje. ALTER TABLEBędą musiały nabyć Sch-Mblokadę pierwszy. Podczas oczekiwania na to wszystkie inne dostęp do tabeli zostaną zablokowane, jak opisano tutaj .

Po uzyskaniu Sch-Mblokady operacja powinna być jednak dość szybka.

Jednym z problemów jest to, że nawet jeśli wiesz, że kolumna w rzeczywistości nie ma żadnych NULLograniczeń, optymalizator zapytań nie ufa temu ograniczeniu, co oznacza, że ​​plany mogą być nieoptymalne.

CREATE TABLE T (X INT NULL)

INSERT INTO T 
SELECT ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values

ALTER TABLE T WITH NOCHECK
  ADD  CONSTRAINT X_NOT_NULL 
    CHECK (X IS NOT NULL) ; 

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Porównaj to z prostszym

ALTER TABLE T ALTER COLUMN X INT NOT NULL

SELECT *
FROM T 
WHERE X NOT IN (SELECT X FROM T)

Jednym z możliwych problemów, które możesz napotkać podczas zmiany definicji kolumny w ten sposób, jest to, że musi on nie tylko odczytać wszystkie wiersze, aby sprawdzić, czy spełniają warunek, ale może także faktycznie wykonać zarejestrowane aktualizacje wierszy .

Możliwym rozwiązaniem w połowie drogi może być dodanie ograniczenia sprawdzania WITH CHECK. Będzie to wolniejsze niż WITH NOCHECKw przypadku konieczności odczytu wszystkich wierszy, ale pozwala optymalizatorowi zapytań podać prostszy plan w powyższym zapytaniu i powinno unikać potencjalnego problemu z zalogowanymi aktualizacjami.

Zamiast zmieniać kolumnę można dodać CHECKograniczenie tabeli za pomocą NOCHECKopcji:

ALTER TABLE MyTable WITH NOCHECK
  ADD  CONSTRAINT MyColumn_NOT_NULL 
    CHECK (MyColumn IS NOT NULL) ;