Zmiana kolumny z NOT NULL na NULL - Co się dzieje pod maską?

Mamy tabelę z rzędami 2.3B. Chcielibyśmy zmienić kolumnę z NOT NULL na NULL. Kolumna jest zawarta w jednym indeksie (nie indeks klastrowany ani indeks PK). Typ danych się nie zmienia (jest to INT). Po prostu nullability. Oświadczenie jest następujące:

Alter Table dbo.Workflow Alter Column LineId Int NULL

Operacja trwa ponad 10, zanim ją zatrzymamy (jeszcze nie dopuściliśmy do jej zakończenia, ponieważ jest to operacja blokująca i trwała zbyt długo). Prawdopodobnie skopiujemy tabelę na serwer deweloperski w celu sprawdzenia, jak długo to faktycznie potrwa. Ale jestem ciekawy, czy ktoś wie, co robi SQL Server pod maską podczas konwersji z NOT NULL na NULL? Czy indeksy, których to dotyczy, będą musiały zostać odbudowane? Wygenerowany plan zapytań nie wskazuje, co się dzieje.

Tabela, o której mowa, jest klastrowana (nie kupa).

Jak wspomniano w @Souplex w komentarzach, jednym z możliwych wyjaśnień może być to, że kolumna ta jest pierwszą NULLkolumną z możliwością indeksowania w indeksie nieklastrowanym, w którym uczestniczy.

Dla następującej konfiguracji

CREATE TABLE Foo
  (
     A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     B CHAR(1) NOT NULL DEFAULT 'B'
  )

CREATE NONCLUSTERED INDEX ix
  ON Foo(B);

INSERT INTO Foo
            (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
       master..spt_values v2 

sys.dm_db_index_physical_stats pokazuje, że nieklastrowany indeks ixma 248 stron liści i jedną stronę główną.

Wygląda typowy wiersz na stronie liścia indeksu

I na stronie głównej

Następnie bieganie ...

CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;


SELECT Operation, 
       Context,
       ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
       COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context

Zwrócony

+-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+

Ponownie sprawdzając liść indeksu, wyglądają teraz wiersze

oraz wiersze na stronach wyższego poziomu, jak poniżej.

Każdy wiersz został zaktualizowany i teraz zawiera dwa bajty dla liczby kolumn wraz z innym bajtem dla NULL_BITMAP.

Ze względu na dodatkową szerokość wiersza indeks nieklastrowany ma teraz 285 stron liści, a teraz dwie strony poziomu pośredniego wraz ze stroną główną.

Plan wykonania dla

 ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;

wygląda następująco

Spowoduje to utworzenie zupełnie nowej kopii indeksu zamiast aktualizowania istniejącej i konieczności dzielenia stron.

Na pewno odtworzy indeks nieklastrowany, a nie tylko zaktualizuje metadane. Jest to testowane na SQL 2014 i naprawdę nie powinno być testowane w systemie produkcyjnym:

CREATE TABLE [z](
    [a] [int] IDENTITY(1,1) NOT NULL,
    [b] [int] NOT NULL,
 CONSTRAINT [c_a] PRIMARY KEY CLUSTERED  ([a] ASC))
go
CREATE NONCLUSTERED INDEX [nc_b] on z (b asc)
GO
insert into z (b)
values (1);

A teraz część zabawy:

DBCC IND (0, z, -1)

To da nam strony bazy danych, w których przechowywana jest tabela i indeks nieklastrowany.

Znajdź PagePIDgdzie IndexID2 i PageType2, a następnie wykonaj następujące czynności:

DBCC TRACEON(3604) --are you sure that you are allowed to do this?

i wtedy:

dbcc page (0, 1, PagePID, 3) with tableresults

Zauważ, że w nagłówku znajduje się pusta mapa bitowa:

Teraz zróbmy:

alter table z alter Column b int null;

Jeśli jesteś naprawdę niecierpliwy, możesz spróbować ponownie uruchomić dbcc pagepolecenie, ale to się nie powiedzie, więc sprawdźmy ponownie przydział za pomocą DBCC IND (0, z, -1). Strona zostanie przeniesiona jak za pomocą magii.

Tak więc zmiana zerowalności kolumny wpłynie na przechowywanie indeksów nieklastrowych pokrywających tę kolumnę, ponieważ metadane muszą zostać zaktualizowane, a później nie trzeba będzie odbudowywać indeksów.

Wiele ALTER TABLE ... ALTER COLUMN ...operacji można wykonać ONLINEpocząwszy od SQL Server 2016, ale:

ALTER TABLE (Transact-SQL)

• Zmiana kolumny z NOT NULLna NULLnie jest obsługiwana jako operacja online, gdy do zmienionej kolumny odwołują się indeksy nieklastrowane.