Правильное создание схемы базы данных является важным элементом в любом процессе проектирования и разработки базы данных. Однако, по мере появления новых требований, становится необходимо вносить изменения в схему таблицы. Знание того, как обрабатывать эти изменения, важно для эффективного управления базой данных.
Когда вы изменяете схему таблицы в SQL Server, за кулисами могут происходить следующие вещи:
- Изменяется только метаданные, содержащие информацию о таблице. Это включает информацию, полученную путем запроса каталоговых представлений, таких как sys.columns.
- SQL Server анализирует существующие данные, чтобы убедиться, что они согласуются с внесенными изменениями в схему.
- SQL Server может изменить физические данные, хранящиеся в каждой строке.
Чтобы увидеть эти изменения на практике, мы можем использовать неопубликованную команду DBCC PAGE. Эта команда позволяет нам изучать физическое хранение данных на странице, содержащей строки. SQL Server хранит данные на страницах размером 8 КБ, и операции ввода-вывода на диск выполняются на уровне страницы.
Давайте создадим тестовую таблицу и посмотрим, как данные хранятся:
USE tempdb
GO
CREATE TABLE tbTestChange
(
PK_id INT NOT NULL PRIMARY KEY,
Col1 SMALLINT NOT NULL,
Col2 VARCHAR(10) NOT NULL,
Col3 CHAR(5) NOT NULL
)
Теперь давайте изучим метаданные для этой таблицы:
SELECT c.name AS column_name, c.column_id, t.name AS column_type_name, c.max_length, c.is_nullable
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE c.object_id = object_id('tbTestChange')
ORDER BY c.column_id
После вставки некоторых данных в таблицу, мы можем использовать команду DBCC PAGE, чтобы увидеть, как данные физически хранятся:
INSERT INTO tbTestChange
SELECT 1, 100, 'AAAAAA', 'aaaa'
UNION
SELECT 2, 101, 'BBBBBBB', 'bbbbb'
DBCC PAGE('tempdb', 1, 157, 1)
Анализируя вывод команды DBCC PAGE, мы можем понять физическую структуру данных на странице. Каждая строка представлена дампом памяти, а массив смещений строк указывает позицию каждой строки на странице.
Теперь давайте рассмотрим различные сценарии, когда изменение схемы таблицы влияет только на метаданные:
Добавление столбца с возможным значением NULL:
ALTER TABLE tbTestChange
ADD Col4 SMALLINT NULL
Метаданные обновляются для включения нового столбца, но физические страницы данных остаются неизменными.
Изменение размера переменного столбца:
ALTER TABLE tbTestChange
ALTER COLUMN Col2 VARCHAR(20) NULL
Опять же, обновляются только метаданные, а физические страницы данных остаются прежними.
Важно отметить, что эти изменения могут быть полезны при добавлении столбцов к большим таблицам, так как они не требуют изменения физических страниц данных. Однако решение о том, должен ли столбец иметь возможное значение NULL или быть обязательным для заполнения, должно основываться на бизнес-логике, а не на времени, затрачиваемом на обновление таблицы.
Понимая, как SQL Server обрабатывает изменения схемы, вы можете эффективно управлять вашей базой данных и обеспечить согласованность данных.
Ссылки:
- sys.columns (Transact-SQL)
- DBCC PAGE (Transact-SQL)
- Inside Microsoft SQL Server 2005: The Storage Engine by Kalen Delaney