Добавить новый столбец в таблицу большой базы данных

Ответ 1

Да, это действительно выполнимо.

Добавление столбца, где NULL является приемлемым и не имеет значения по умолчанию, не требует долговременной блокировки для добавления данных в таблицу.

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

Как это работает вообще:

+---------------------+------------------------+-----------------------+
| Column is Nullable? | Default Value Supplied | Result                |
+---------------------+------------------------+-----------------------+
| Yes                 | No                     | Quick Add (caveat)    |
| Yes                 | Yes                    | Long running lock     |
| No                  | No                     | Error                 |
| No                  | Yes                    | Long running lock     |
+---------------------+------------------------+-----------------------+

Предостережение:

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

Изменить → @MartinSmith указал, что растровое изображение NULL будет расширяться только при изменении строки, большое спасибо. Однако, как он также указывает, если размер строки расширяется за пределы байта 8060 байтов в SQL Server 2012, тогда может потребоваться длительная блокировка. Большое спасибо * 2.

Вторая оговорка:

Проверьте его.

Третий и последний оговорки:

Нет, проверьте его.

Ответ 2

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

USE [MyDB]
GO

ALTER TABLE [dbo].[Customer] ADD [CustomerTypeId] TINYINT NULL
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_CustomerTypeId] DEFAULT 1 FOR [CustomerTypeId]
GO
DECLARE @batchSize bigint = 5000
    ,@rowcount int
    ,@MaxID int;

SET @rowcount = 1
SET @MaxID = 0

WHILE @rowcount > 0
BEGIN
    ;WITH upd as (
        SELECT TOP (@batchSize)
            [ID]
            ,[CustomerTypeId]
        FROM [dbo].[Customer] (NOLOCK)
        WHERE [CustomerTypeId] IS NULL
            AND [ID] > @MaxID
        ORDER BY [ID])

    UPDATE upd
          SET [CustomerTypeId] = 1
              ,@MaxID = CASE WHEN [ID] > @MaxID THEN [ID] ELSE @MaxID END

    SET @rowcount = @@ROWCOUNT
    WAITFOR DELAY '00:00:01'
END;

ALTER TABLE [dbo].[Customer]  ALTER COLUMN [CustomerTypeId] TINYINT NOT NULL;
GO

ALTER TABLE [dbo].[Customer] ADD [CustomerTypeId] TINYINT NULL изменяет только метаданные (замки Sch-M) и время блокировки не зависит от количества строк в таблице

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

Наконец, когда заполняется весь новый столбец, я меняю его на NOT NULL

Ответ 3

Никто не может сказать, сколько времени будет стоить операция, так как это зависит от многих факторов.

Вы не должны беспокоиться о самих операциях, потому что SQL Server делает все правильно:

Механизм Database Engine использует блокировку модификации схемы (Sch-M) во время (DDL), например, добавление столбец или отбрасывание таблицы. В течение времени, которое он проводит, Sch-M блокировка предотвращает параллельный доступ к таблице. Это означает, что Sch-M блокировка блокирует все внешние операции до тех пор, пока блокировка не будет отпущена.

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

ИЗМЕНИТЬ:

Здесь вы можете найти дополнительную информацию о своем вопросе. Как правило, Matt Whitfield является правильным и

Единственный раз, когда добавление столбца в таблицу приводит к операции с размером данных (т.е. операции, которая изменяет каждую строку в table), когда новый столбец имеет ненулевое значение по умолчанию.

и когда

Новый столбец имеет значение NULL с по умолчанию NULL. Метаданные таблицы записывает тот факт, что новый столбец существует, но не может находиться в запись. Вот почему нулевой битмап также имеет количество чисел столбцов в этой конкретной записи. SQL Server может решить, столбец присутствует в записи или нет. Итак - это НЕ операция по размеру данных - существующие записи таблицы не обновляются при добавлении нового столбца. Записи будут обновляться только тогда, когда они обновляются для некоторых других операций.

Ответ 4

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