Как хранятся значения по умолчанию?

Автор: Paul Randal, How are default column values stored?

На занятии возник интересный вопрос: как хранится значение столбца по умолчанию и что происходит, если некоторые строки уже существуют при добавлении столбца, а затем значение по умолчанию меняется?

Пример сценария:

1. Шаг 1: Создать таблицу с двумя столбцами
2. Шаг 2: Добавить 10 строк
3. Шаг 3: Добавить третий столбец в таблицу с ненулевым значением по умолчанию
4. Шаг 4: Удалить значение по умолчанию для третьего столбца
5. Шаг 5: Добавить новое значение по умолчанию для третьего столбца

И при выборке изначальных 10 строк можно продемонстрировать, что третий столбец использует первоначальное значение по умолчанию, установленное на шаге 3. (Не имеет значения, добавлялись ли строки между шагами 3 и 4.)

Это означает, что при добавлении нового столбца должно храниться два значения по умолчанию: одно для уже существующих строк, у которых нет нового столбца, и одно для любых новых строк. Изначально эти два значения будут одинаковыми, но значение для новых строк может измениться (например, на шагах 4 и 5 выше), не затрагивая старые строки. Это работает потому, что после добавления нового столбца (шаг 3 выше) невозможно добавить строки, у которых нет нового столбца.

Именно так всё и работает. Давайте исследуем!

Сначала я создам простую базу данных и тестовую таблицу и вставлю 10 строк. Я использую модель простого восстановления, чтобы очистить журнал с помощью контрольной точки:

CREATE DATABASE [Company];
ALTER DATABASE [Company] SET RECOVERY SIMPLE;
GO
USE [Company];
GO

-- Создадим тестовую таблицу для использования
CREATE TABLE [Test] ([c1] INT IDENTITY, [c2] INT AS ([c1]));
GO
INSERT INTO [Test] DEFAULT VALUES;
GO 10
SELECT * FROM [Test];
GO

c1          c2
----------- -----------
1           1
2           2
3           3
4           4
5           5
6           6
7           7
8           8
9           9
10          10

Теперь я очищу журнал, добавлю третий столбец со значением по умолчанию и посмотрю, в какие системные таблицы были добавлены записи из-за этого изменения:

CHECKPOINT;
GO

-- Добавим столбец со значением по умолчанию
ALTER TABLE [Test] ADD [c3] CHAR (6) NOT NULL CONSTRAINT [OriginalDefault] DEFAULT 'BEFORE';
GO

SELECT [AllocUnitName] FROM fn_dblog (NULL, NULL)
WHERE [Operation] = 'LOP_INSERT_ROWS';
GO

AllocUnitName
-----------------------
sys.syscolpars.clst
sys.syscolpars.nc
sys.sysrscols.clst
sys.sysseobjvalues.clst
sys.sysschobjs.clst
sys.sysschobjs.nc1
sys.sysschobjs.nc2
sys.sysschobjs.nc3
sys.sysobjvalues.clst

Отлично. Эти системные таблицы выполняют следующие функции:

• sys.syscolpars: определения столбцов таблицы (реляционные метаданные)
• sys.sysrscols: определения столбцов набора строк (метаданные подсистемы хранения — информация для интерпретации структур записей на страницах)
• sys.sysseobjvalues: различные значения подсистемы хранения разного назначения
• sys.sysschobjs: реляционные объекты (например, таблицы, ограничения)
• sys.sysobjvalues: различные реляционные значения разного назначения

Я посмотрю в этих таблицах, как вставленные строки связаны с нашей таблицей. Сначала мне понадобится несколько идентификаторов (используя для этого мою удобную процедуру):

EXEC sp_allocationmetadata N'Test';
GO

Object Name Index ID Partition ID      Alloc Unit ID     Alloc Unit Type First Page Root Page First IAM Page
----------- -------- ----------------- ----------------- --------------- ---------- --------- --------------
Test        0        72057594040549376 72057594045792256 IN_ROW_DATA     (1:247)    (0:0)     (1:288)

И теперь я могу запросить эти системные таблицы. Обратите внимание, что это «скрытые» системные таблицы, поэтому вы не можете их запрашивать, если не подключитесь с помощью выделенного административного соединения (DAC). Самый простой способ сделать это — добавить префикс «admin:» к строке подключения SSMS (и если вы подключаетесь к удалённому серверу, на сервере должна быть включена опция настройки sp_configure remote admin connections). Убедитесь, что после подключения вы используете правильную базу данных, так как DAC помещает вас в базу master.

SELECT * FROM sys.syscolpars WHERE [id] = OBJECT_ID (N'Test');
GO

id        number colid name xtype utype length prec scale collationid status maxinrow xmlns dflt        chk idtval
--------- ------ ----- ---- ----- ----- ------ ---- ----- ----------- ------ -------- ----- ----------- --- ---------
245575913 0      1     c1   56    56    4      10   0     0           5      4        0     0           0   0x0A000000010000000100000000
245575913 0      2     c2   56    56    4      10   0     0           209    4        0     0           0   NULL
245575913 0      3     c3   175   175   6      0    0     872468488   3      6        0     261575970   0   NULL

Это реляционные определения столбцов в таблице, и вы можете видеть, что у c3 указано ограничение по умолчанию с идентификатором 261575970.

SELECT * FROM sys.sysschobjs WHERE [id] = 261575970;
GO

id          name            nsid nsclass status type pid       pclass intprop created                 modified                status2
----------- --------------- ---- ------- ------ ---- --------- ------ ------- ----------------------- ----------------------- -------
261575970   OriginalDefault 1    0       131072 D    245575913 1      3       2017-04-26 13:37:42.463 2017-04-26 13:37:42.463 0

Это ограничение с именем OriginalDefault, типа D (default — значение по умолчанию), и само значение по умолчанию имеет идентификатор 245575913.

SELECT * FROM sys.sysobjvalues WHERE [objid] = 261575970;
GO

valclass objid     subobjid valnum value imageval
-------- --------- -------- ------ ----- ----------------------
1        261575970 0        0      2     0x28274245464F52452729

И столбец imageval содержит значение по умолчанию в виде шестнадцатерично-закодированных ASCII-значений. Используя таблицу ASCII на Wikipedia, значение равно («BEFORE»), включая круглые скобки.

Итак, это значение по умолчанию для новых строк. А что насчёт значения по умолчанию для уже существующих строк?

SELECT * FROM sys.sysrscols WHERE [rsid] = 72057594040549376;
GO

rsid              rscolid hbcolid rcmodified ti   cid       ordkey maxinrowlen status offset nullbit bitpos colguid
----------------- ------- ------- ---------- ---- --------- ------ ----------- ------ ------ ------- ------ -------
72057594040549376 1       1       10         56   0         0      0           128    4      1       0      NULL
72057594040549376 2       2       10         56   0         0      0           128    8      2       0      NULL
72057594040549376 3       3       0          1711 872468488 0      0           640    12     3       0      NULL

Это определения столбцов таблицы на уровне подсистемы хранения. Значение status указывает, что значение может отсутствовать в строке, и откуда его нужно брать по умолчанию.

SELECT * FROM sys.sysseobjvalues WHERE [id] = 72057594040549376;
GO

valclass id                subid valnum value  imageval
-------- ----------------- ----- ------ ------ --------
1        72057594040549376 3     0      BEFORE NULL

И вот он — механизм хранения значения по умолчанию в подсистеме хранения для столбца c3 для тех строк, которые существовали до добавления c3.

Теперь я создам контрольную точку, удалю ограничение по умолчанию и посмотрю, что произошло в журнале:

CHECKPOINT;
GO

ALTER TABLE [Test] DROP CONSTRAINT [OriginalDefault];
GO

SELECT [AllocUnitName] FROM fn_dblog (NULL, NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS';
GO

AllocUnitName
---------------------
sys.sysobjvalues.clst
sys.sysschobjs.nc1
sys.sysschobjs.nc2
sys.sysschobjs.nc3
sys.sysschobjs.clst

Итак, реляционное значение по умолчанию удаляется в обратном порядке по сравнению с тем, как оно добавлялось. Обратите внимание, что значение по умолчанию в подсистеме хранения не было удалено.

Теперь я создам новое ограничение по умолчанию для столбца c3:

CHECKPOINT;
GO

ALTER TABLE [Test] ADD CONSTRAINT [NewDefault] DEFAULT 'AFTER' FOR [c3];
GO

SELECT [AllocUnitName] FROM fn_dblog (NULL, NULL)
WHERE [Operation] = 'LOP_INSERT_ROWS';
GO

AllocUnitName
---------------------
sys.sysschobjs.clst
sys.sysschobjs.nc1
sys.sysschobjs.nc2
sys.sysschobjs.nc3
sys.sysobjvalues.clst

И повторное выполнение различных запросов приводит нас к новому реляционному значению по умолчанию для столбца («AFTER»), включая круглые скобки.

Итак, просто чтобы доказать то, что я сказал до начала исследования, я добавлю десять новых строк, которые будут иметь значение c3 равное AFTER, а затем выполню запрос к таблице, и я увижу, что изначальным десяти строкам, у которых нет c3, будет присвоено исходное значение по умолчанию BEFORE:

INSERT INTO [Test] DEFAULT VALUES;
GO 10

SELECT * FROM [Test];
GO

c1          c2          c3
----------- ----------- ------
1           1           BEFORE
2           2           BEFORE
3           3           BEFORE
4           4           BEFORE
5           5           BEFORE
6           6           BEFORE
7           7           BEFORE
8           8           BEFORE
9           9           BEFORE
10          10          BEFORE
11          11          AFTER 
12          12          AFTER 
13          13          AFTER 
14          14          AFTER 
15          15          AFTER 
16          16          AFTER 
17          17          AFTER 
18          18          AFTER 
19          19          AFTER 
20          20          AFTER

Надеюсь, вы нашли это интересным! (И не забудьте разорвать соединение DAC).