SQL-Ex blog

Напомню, что я годами говорил о том, что вы можете использовать DATETIME2(3) как безотказную замену DATETIME и иметь лучшую грануляцию (1 миллисекунду вместо 3-х миллисекунд) для меньшего на 12,5% хранилища (1 байт на столбец в строке). Комментатор намекал, что поскольку мой оператор конфликтует с документацией, то я неправ. Как оказалось, неправильной была документация, однако в процессе я узнал что-то новое!

Первый байт значения datetime2 хранит точность значения, это означает, что фактическое хранилище, необходимое для значения datetime2, - это размер хранилища, указанный в таблице выше, плюс 1 дополнительный байт для хранения точности. Это делает максимальный размер значения datetime2 равным 9 байтам - 1 байт хранит точность плюс 8 байтов для хранения данных с максимальной точностью.

Это неправда, и ниже приводится код, доказывающий это. Однако я подозреваю, что написавший этот комментарий перепутал размер DATETIME2 при преобразовании в VARBINARY (о котором я писал здесь) с тем, что находится в хранилище. На самом деле я не могу винить технического писателя, так как сам делал такое предположение. Когда я обнаружил, что ошибся, это привело к написанию серии статей о том, как хранятся данные в SQL Server.

О чем этот пост

Есть отличный аргумент в пользу того, чтобы придерживаться DATETIME, поскольку имеются функции, которые опираются на стандарты ANSI и просто не работают с DATETIME2. Как я заметил в недавнем посте, способ хранения данных радикально различается между DATETIME и DATETIME2, так что это имеет смысл. Этот пост ни в коей мере не направлен против использования DATETIME.

Помимо прочего, я не могу сосчитать число раз, когда я слышал вариации темы: "Имеется ли официальный источник Microsoft для этой информации? Мой ИТ-отдел не хочет верить на слово случайному блоггеру».

Microsoft Docs может редактировать кто угодно

SQL Server Books Online мигрировали на Microsoft Docs - хостится на GitHub - несколько лет назад, приблизительно когда вышел SQL Server 2016. За это время я внес множество мелких исправлений, особенно во время написания трех технических книг об SQL Server, хотя, главным образом, в области опечаток. Что касается фактически неверной документации, мне требуется уверенность, что я обдумал все аспекты. Кроме того, мои тексты не соответствуют стилю официальной документации. Другими словами, я не бы уверен, что мои изменения были на 100% точны.

Paul White научил меня тому, чего я не знал о работе DATETIME2, особенно в области обработки пакетного режима в SQL Server:

Все данные в пакете представлены восьмибайтовым значением, в конкретном нормализованном формате, независимо от типов данных.

Конечно, это относится к типу данных в памяти, а не на диске, поэтому мое утверждение, что DATETIME2(3) занимает 7 байтов в хранилище не было опровергнуто. Однако проверка моей работы перед внесением исправления в документацию была правильным решением. Paul также напомнил мне, что сжатие строки изменяет способ хранения DATETIME2 в соответствии с определенной формулой.

На следующий день Microsoft ответила, что в документацию были внесены исправления с учетом этих фактов.

Исправленная документация говорила:

¹При условии, что значения представляют собой несжатое построчное хранилище. Использование сжатия данных или поколоночное хранение могут изменить размер хранилища для каждой точности. Кроме того, размер хранилища на диске и в памяти могут отличаться. Например, значения datetime2 всегда требуют 8 байтов в памяти при использовании пакетного режима.

²При преобразовании значения datetime2 к типу varbinary дополнительный байт добавляется к значению varbinary, чтобы сохранить точность.

Покажите ваш код

Это был пример кода, который я использовал для объяснения моего мнения об ошибочности документации.

В моем примере я окружил столбец DATETIME2(3) двумя столбцами CHAR(3), чтобы было легче выделить их в шестнадцатеричном представлении.

USE tempdb;

GO

-- Создаем таблицу

CREATE TABLE [dbo].[test] (

     [c1] [char](3) NOT NULL,

     [d1] [DATETIME2](3) NOT NULL,

     [c2] [char](3) NOT NULL,

)

GO

-- Устанавливаем значения по умолчанию

ALTER TABLE [dbo].[test] ADD DEFAULT ('aaa') FOR [c1]

ALTER TABLE [dbo].[test] ADD DEFAULT SYSUTCDATETIME() FOR [d1]

ALTER TABLE [dbo].[test] ADD DEFAULT ('bbb') FOR [c2]

GO

-- Вставляем 100 строк

INSERT INTO dbo.test DEFAULT VALUES

GO 100

-- Получаем pageID для нашей таблицы

DBCC IND(2, 'test', 1);

-- Наблюдаем hex (подставить сюда корректное значение pageID)

DBCC TRACEON(3604);

DBCC PAGE(2, 1, 2512, 2);

Вот что я вижу в заголовке страницы и двух первых строках (каждая строка начинается с 0x50001100):



Когда я выполняю DBCC PAGE с уровнем детализации 3 вместо 2, он разбивает каждую строку замечательным образом (для сокращения я включил только первую строку):



Обратите внимание на Length 7 в последнем примере.

Заключение

Даже хотя я знаю, что документация ошибалась, я решил попросить других людей проверить мою работу. Это дало мне возможность изучить что-то новое и получить еще лучший результат для документации по сравнению с моим узким взглядом на "несжатые данные с построчным хранением". Хотя это может быть состоянием по умолчанию для данных в SQL Server, оно не охватывает всего, и это не менее важно.