ONLINE добавление нового столбца с DEFAULT в SQL Server 2012

В текущих версиях SQL Server ( SQL Server 2008R2 и ниже ) операция добавления нового столбца NOT NULL с DEFAULT-значением может занять значительное время, если таблица имеет большой размер. Для небольших таблиц эта операция допустима, но, когда объём данных в таблице велик, то мы получаем блокировку Sch-M для таблицы на длительное время, т.к. при этой операции происходит построчное заполнение нового столбца значением по умолчанию.

В SQL Server 2012 ситуация кардинально изменилась, теперь при добавлении в таблицу нового NOT NULL столбца с DEFAULT-значением мы не производим физического изменения данных построчно, а лишь изменяем таблицу на уровне метаданных, поэтому операция практически мгновенна.

Для демонстрации создадим небольшую таблицу с двумя полями:

set nocount on;
go
if object_id ( N'dbo.test_table', N'U' ) is not null
  drop table dbo.test_table;
go
create table dbo.test_table ( id int identity primary key 
                            , val01 int not null
                            );
go
insert into dbo.test_table
select checksum ( newid() ) % 1000;
go 1000 

Теперь с помощью недокументированной инструкции DBCC PAGE взглянем на физическую структуру данных таблицы, но прежде с помощью псевдо столбца %%physloc%% определим страницу, на которой расположена первая запись нашей таблицы:

select fn.* 
  from dbo.test_table tt
  cross apply sys.fn_PhysLocCracker( %%physloc%% ) fn 
  where id = 1

Теперь, когда мы знаем номер файла и номер страницы, выполним скрипт:

dbcc traceon ( 3604 );
declare @db_id int = db_id();
dbcc page( @db_id, 1, 4522, 3 );

Обратите внимание, что последний номер LSN обновлённый на этой странице имеет значение (614:7120:25), а размер записи Slot 0 = 15 байт.

Теперь добавим в таблицу новый NOT NULL столбец с значением по умолчанию:

alter table dbo.test_table
  add val02 int not null default ( 10 );

Сделаем выборку нескольких строк из таблицы, чтобы убедиться, что в новом столбце действительно есть записи:

select top 10 * from dbo.test_table

Теперь повторно обратимся к нашей странице

dbcc traceon ( 3604 );
declare @db_id int = db_id();
dbcc page( @db_id, 1, 4522, 3 );

Заголовок страницы не изменился. Последний номер LSN по-прежнему (614:7120:25), а это значит, что физически никаких изменений не произошло. При этом мы видим столбец №3 и его значение = 10, но размерность его (physical) = 0, смещение = 0x0.
Т.е. изменения произошли не в самих данных, а лишь на уровне метаданных.

Обратимся к двум внутренним системным представлениям sys.system_internals_partitions и sys.system_internals_partition_columns

select pc.* 
  from sys.system_internals_partitions p
	  join sys.system_internals_partition_columns pc 
      on p.partition_id = pc.partition_id
	where p.object_id = object_id( N'dbo.test_table', N'U' );

В SQL Server 2012 в представлении sys.system_internals_partition_columns появились два новых столбца has_default и default_value именно они показывают информацию о значениях по умолчанию для колонки.

select pc.partition_column_id
     , pc.has_default
     , pc.default_value
  from sys.system_internals_partitions p
	  join sys.system_internals_partition_columns pc 
      on p.partition_id = pc.partition_id
	where p.object_id = object_id( N'dbo.test_table', N'U' );

Именно из этих полей SQL Server понимает, какое значение содержится в 3-ем столбце. Благодаря этой хитрости теперь добавление столбца со значением по умолчанию происходит быстро независимо от того, какой объём данных содержится в таблице, т.к. информация обновляется исключительно на уровне метаданных без модификации самих данных. Такое поведение является стандартным для SQL Server 2012, т.е. нам не нужно включать какие-то дополнительные настройки.

Если мы обновляем строку (любое поле), то DEFAULT-значение будет физически помещено в запись:

update dbo.test_table
  set val01 = val01
where id = 1

Хотя мы и не обновляли столбец val02, который содержит значение по умолчанию, мы обновили строку и этого достаточно, чтобы SQL Server переместил значение по умолчанию в эту запись

dbcc traceon ( 3604 );
declare @db_id int = db_id();
dbcc page( @db_id, 1, 4522, 3 );

На рисунке видно, что запись увеличилась и она теперь равна 19 байтам, а столбец 3 имеет физический размер и смещение. Но эти изменения коснулись только той, записи, которую мы обновляли и если мы посмотрим информацию на странице для следующей записи, то мы увидим, что для неё размерность осталась 15 байт, а физический размер 3-его столбца по-прежнему равен 0.



Теперь сравним, как работает ALTER TABLE ... ADD для SQL Server 2008 SP3 и SQL Server 2012. Для этого создадим относительно большую таблицу, в которой будет 1 млн. записей:

create table dbo.test_table_big ( id int identity, val01 varchar(4000) );
with cte
as
(
select 1 i
union all
select i + 1 from cte
  where i < 1000000 --1 млн.
)
insert into dbo.test_table_big
  select replicate( 'A', 4000 )
    from cte
option (maxrecursion 0);

Таблица dbo.test_table_big имеет размер ~ 4 Гб. Теперь добавим новый столбец со значением по умолчанию для разных версий SQL Server.

set nocount on;
print @@version
set statistics io on;
set statistics time on;

alter table dbo.test_table_big
  add val02 int not null default (10);

Ниже результат работы для
SQL Server 2012:

Microsoft SQL Server 2012 RC0 - 11.0.1913.37 (X64) 
       Dec  9 2011 10:01:51 
       Copyright (c) Microsoft Corporation
       Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 (X64) (Build 7601: Service Pack 1)

SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 39 ms.

SQL Server 2008:

Microsoft SQL Server 2008 (SP3) - 10.0.5416.0 (X64) 
       Aug  5 2011 13:32:37 
       Copyright (c) 1988-2008 Microsoft Corporation
       Enterprise Edition (64-bit) on Windows NT 6.1 (X64) (Build 7601: Service Pack 1)

Table 'Worktable'. Scan count 8, logical reads 2021490, physical reads 2493, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'test_table_big'. Scan count 9, logical reads 1500000, physical reads 332503, read-ahead reads 640576, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 33070 ms,  elapsed time = 688183 ms.

А теперь обратимся ещё к одному полю внутреннего системного представления sys.system_internals_partition_columns и выведем информацию для обеих версий SQL Server:

select pc.partition_column_id
     , pc.modified_count
  from sys.system_internals_partitions p
	  join sys.system_internals_partition_columns pc 
      on p.partition_id = pc.partition_id
	where p.object_id = object_id( N'dbo.test_table_big', N'U' );

SQL Server 2012



SQL Server 2008



Мы опять можем убедиться, что для SQL Server 2012 никаких изменений у столбца №3 не было в отличеи от SQL Server 2008.

Несколько особенностей (ограничений), связанных с ONLINE добавлением нового поля с значением по умолчанию:

• При любом перестроении таблицы (кластерного индекса) все записи с значением по умолчанию перемещаются на страницы таблицы, при этом в sys.system_internals_partition_columns поля has_default и default_value будут иметь значение 0 и null соответственно
• Не все типы данных и значения по умолчанию могут быть добавлены онлайн. Исключение составляют типы BLOB varchar(max), nvarchar(max), varbinary(max) и XML
• Типы, которые не могут быть конвертированны к sql_variant, такие как hierarchy_id, geometry и geography или пользовательские CLR-типы данных
• Выражения по умолчанию, которые возвращают разные значения для каждой строки такие, как NEWID или NEWSEQUENTIALID, не могут быть добавлены онлайн (выражение по умолчанию должно быть константой на этапе выполнения)
• Онлайн добавление столбца работает только в редакции Enterprise Edition
• Т.к. при OFFLINE добавлении столбца происходит физическое обновление данных, то для отслеживания этого факта добавлено новое расширенное событие (XEvent) = alter_table_update_data
• В случае, когда добавляемый столбец увеличивает размер строки до размера более 8060 байт, то такой столбец не может быть добавлен онлайн

Про последнее ограничение поговорим немного подробнее. Для этого создадим ещё одну тестовую таблицу:

if object_id ( N'T', N'U' ) is not null
  drop table T
go
create table T ( c char(1) null
	       , filler char(7998) null
	       , vc1 varchar(50) null
	       , vc2 varchar(50) null
               );
go	

insert into T ( c, vc1, vc2 ) 
values ( 'A', null, null )
     , ( 'B', replicate( 'X', 50 ), null )
     , ( 'C', null, replicate( 'Y', 50 ) )
     , ( 'D', replicate( 'X', 50 ), replicate( 'Y', 50 ) );
go

Таблица создалась, значения мы вставили, но увидели предупреждение: Warning: The table "T" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
Теперь добавим новый столбец

alter table T add some_int int null;
go

Если мы выполним этот скрипт в SQL Server 2008 R2 и ниже, то он выполнится успешно и в таблицу добавится новый столбец в режиме ONLINE, т.е. на уровне метаданных. Но у этого столбца есть ряд ограничений и если мы попробуем выполнить обновление этого поля, то получим ошибку:

update T set some_int = null
	where c = 'A';
update T set some_int = null
	where c = 'B';
update T set some_int = null
	where c = 'C';
update T set some_int = null
	where c = 'D';
go

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 511, Level 16, State 1, Line 7
Cannot create a row of size 8064 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Как вы видите, 3 записи были успешно обновлены, а на 4-ой мы получили ошибку. Что особенно интересно, я обновлял столбец со значением NULL на NULL. Но это не всё. Ниже ещё пара запросов, которые вызовут исключение:

select * into T2 from T;
go


Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

alter table T rebuild;
go


Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable maximum row size of 8060.
The statement has been terminated. 

Т.е., после обновления 3-х полей, мы не можем работать с таблицей.

В SQL Server 2012 подобного не может произойти, т.к. мы получим ошибку на этапе добавления нового столбца:

alter table T add some_int int null;
go

Warning: The table "T" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8064 which is greater than the allowable maximum row size of 8060.
The statement has been terminated.

Таким образом, SQL Server 2012 смог обнаружить проблему до её появления и тем самым препятствовал появлению ситуации при которой мы бы получили таблицу в "нерабочем" состоянии. При этом мы получим ошибку, только если у нас в таблице есть "проблемные" записи. Посмотрим на следующий скрипт:

if object_id ( N'T', N'U' ) is not null
  drop table T
go

create table T ( c char(1) null
	       , filler char(7998) null
	       , vc1 varchar(50) null
	       , vc2 varchar(50) null
               );
go	

insert into T ( c, vc1, vc2 ) 
values ( 'A', null, null )
     , ( 'B', replicate( 'X', 50 ), null )
     , ( 'C', null, replicate( 'Y', 50 ) );
go

alter table T add some_int int null;
go

Скрипт успешно отработал, т.к. все записи помещаются в размер = 8060. Проблемная запись была с идентификатором D. SQL Server 2012 при операции ALTER TABLE ... ADD COLUMN проверяет, что каждая строка помещается в 8060 байт и если возникает исключение хотя бы в одной записи, то операция добавления нового столбца откатывается полностью.

Именно для того, чтобы гарантировать, что каждая запись помещается в 8060 байт SQL Server 2012, при добавлении нового nullable-столбца, выполняет OFFLINE обновление каждой строки в таблице, чтобы гарантировать, что каждая отдельная строка помещается на страницу. Это ещё одно нововведение SQL Server 2012.

Эта ситуация может возникнуть при добавлении нового столбца фиксированной длины или переменного не допускающего NULL-значения столбца со значением по умолчанию к таблице, у которой уже есть потенциальная проблема создания строк свыше максимального размера 8060. Такого поведения (обновление каждой строки, чтобы проверить, что они все помещаются на страницу после того, как столбец добавлен), не происходит при добавлении nullable-столбца переменной длины или разреженного столбца фиксированной длины.

Англоязычные подробности:

http://msdn.microsoft.com/ru-ru/library/ms190273(v=sql.110).aspx
http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/
http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/