Как добавить столбцы NOT NULL в большую таблицу в SQL Server?

Ответ 1

Я столкнулся с этой проблемой и для своей работы. И мое решение идет по №2.

Вот мои шаги (я использую SQL Server 2005):

1) Добавьте столбец в таблицу со значением по умолчанию:

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('')

2) Добавьте ограничение NOT NULL с опцией NOCHECK. NOCHECK не применяет существующие значения:

ALTER TABLE MyTable WITH NOCHECK
ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL)

3) Инкрементно обновляйте значения в таблице:

GO
UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL
GO 1000

• Оператор обновления будет обновлять максимум 3000 записей. Это позволяет сохранить кусок данных в то время. Я должен использовать "MyColumn IS NULL", потому что моя таблица не имеет первичного ключа последовательности.

• GO 1000 будет выполнять предыдущий оператор 1000 раз. Это позволит обновить 3 миллиона записей, если вам нужно просто увеличить это число. Он будет продолжать выполняться до тех пор, пока SQL Server не вернет 0 записей для инструкции UPDATE.

Ответ 2

Вот что я хотел бы попробовать:

• Сделайте полную резервную копию базы данных.
• Добавить новый столбец, допустив null - не устанавливать значение по умолчанию.
• Установите SIMPLE-восстановление, которое обрезает журнал транзакций, как только будет завершена каждая партия.
• SQL: ALTER DATABASE XXX SET RECOVERY SIMPLE
• Запустите обновление пакетами, как описано выше, после каждого из них.
• Reset новый столбец, который больше не допускает null.
• Вернитесь к нормальному ПОЛНОМ восстановлению.
• SQL: ALTER DATABASE XXX SET RECOVERY FULL
• Резервное копирование базы данных снова.

Использование модели восстановления SIMPLE не прекращает ведение журнала, но значительно снижает его влияние. Это связано с тем, что сервер удаляет информацию о восстановлении после каждой фиксации.

Ответ 3

Вы можете:

• Запустите транзакцию.
• Возьмите блокировку записи в исходной таблице, чтобы никто не писал ей.
• Создайте теневую таблицу с новой схемой.
• Перенесите все данные из исходной таблицы.
• выполнить sp_rename, чтобы переименовать старую таблицу.
• выполните sp_rename, чтобы переименовать новую таблицу.
• Наконец, вы совершаете транзакцию.

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

Ответ 4

Просто обновите это с помощью последней информации.

В SQL Server 2012 это теперь можно выполнить как онлайн-операцию в следующих обстоятельствах.

• Только Enterprise Edition
• По умолчанию должна быть константа времени выполнения

Для второго требования примеры могут быть литеральной константой или функцией, такой как GETDATE(), которая оценивает одно значение для всех строк. По умолчанию NEWID() будет не квалифицироваться и все равно будет обновлять все строки там и потом.

По умолчанию, которые квалифицируют SQL Server, они оценивают их и сохраняют результат как значение по умолчанию в метаданных столбца, так что это не зависит от созданного по умолчанию ограничения (которое даже может быть отброшено, если больше не требуется). Это можно просмотреть в sys.system_internals_partition_columns. Значение не записывается в строки до следующего раза, когда они будут обновлены.

Подробнее об этом здесь: онлайн не нуль со столбцами значений добавить в SQL Server 2012

Ответ 5

Я думаю, что это зависит от используемого вами SQL-вкуса, но что, если вы взяли опцию 2, но в самом конце измените таблицу таблиц так, чтобы она не была равна нулю по умолчанию?

Было бы быстро, так как он видит, что все значения не равны нулю?

Ответ 6

Если вам нужен столбец в одной таблице, вам просто нужно это сделать. Теперь вариант 3 потенциально является лучшим для этого, потому что вы все еще можете использовать базу данных "вживую", пока эта операция продолжается. Если вы используете вариант 1, таблица блокируется во время операции, а затем вы действительно застреваете.

Если вам все равно, находится ли столбец в таблице, я полагаю, что сегментный подход является лучшим. Хотя, я действительно стараюсь избегать этого (до такой степени, что я этого не делаю), потому что, как говорит Чарльз Бретана, вам нужно убедиться и найти все места, которые обновляют/вставляют эту таблицу и изменяют их. Тьфу!

Ответ 7

У меня была аналогичная проблема, и я пошел на ваш вариант №2. Это занимает 20 минут таким образом, в отличие от 32 часов в другую сторону!!! Огромная разница, спасибо за подсказку. Я написал полную запись в блоге об этом, но вот важный sql:

Alter table MyTable
Add MyNewColumn char(10) null default '?';
go

update MyTable set MyNewColumn='?' where MyPrimaryKey between 0 and 1000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 1000000 and 2000000
go
update MyTable set MyNewColumn='?' where MyPrimaryKey between 2000000 and 3000000
go
..etc..

Alter table MyTable
Alter column MyNewColumn char(10) not null;

И запись в блоге, если вам интересно: http://splinter.com.au/adding-a-column-to-a-massive-sql-server-table

Ответ 8

У меня была аналогичная проблема, и я пошел с измененным подходом №3. В моем случае база данных находилась в режиме восстановления SIMPLE, и в таблицу, в которую должен был быть добавлен столбец, не ссылались никакие ограничения FK.

Вместо создания новой таблицы с той же схемой и копирования содержимого исходной таблицы я использовал синтаксис SELECT... INTO.

Согласно Microsoft (http://technet.microsoft.com/en-us/library/ms188029(v=sql.105).aspx)

Объем ведения журнала для SELECT... INTO зависит от модели восстановления в действительности для базы данных. В рамках простой модели восстановления или объемная модель восстановления, объемные операции минимально регистрируются. С минимальное ведение журнала, использование оператора SELECT... INTO может быть больше чем создание таблицы, а затем заполнение таблицы Инструкция INSERT. Для получения дополнительной информации см. Операции, которые могут быть Минимально зарегистрированный.

Последовательность шагов:

1. Переместите данные из старой таблицы в новую, добавив новый столбец со значением по умолчанию

 SELECT  table.*,   cast (‘default’ as nvarchar(256)) new_column
 INTO    table_copy 
 FROM    table

2.Заверните старую таблицу

 DROP TABLE  table

3.Зарегистрируйте вновь созданную таблицу

 EXEC sp_rename 'table_copy',  ‘table’

4.Создание необходимых ограничений и индексов в новой таблице

В моем случае таблица имела более 100 миллионов строк, и этот подход завершался быстрее, чем подход № 2, а рост журнального пространства был минимальным.

Ответ 9

Допустил, что это старый вопрос. Недавно мой коллега сказал мне, что он смог сделать это в одном объявлении таблицы alter таблицы на 13.6M строк. Он завершился в течение секунды в SQL Server 2012. Я смог подтвердить то же самое на таблице с 8M строк. Что-то изменилось в более поздней версии SQL Server?

Alter table mytable add mycolumn char(1) not null default('N');

Ответ 10

1) Добавьте столбец в таблицу со значением по умолчанию:

ALTER TABLE MyTable ADD MyColumn int default 0

2) Периодически обновляйте значения в таблице (такой же эффект, как и принятый ответ). Отрегулируйте количество обновляемых записей в вашей среде, чтобы избежать блокировки других пользователей/процессов.

declare @rowcount int = 1

while (@rowcount > 0)
begin           

    UPDATE TOP(10000) MyTable SET MyColumn = 0 WHERE MyColumn IS NULL       
    set @rowcount = @@ROWCOUNT

end

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

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL

Ответ 11

Я бы использовал CURSOR вместо UPDATE. Курсор будет обновлять все соответствующие записи в пакетном режиме, записывать по записи - требуется время, но не блокирует таблицу.

Если вы хотите избежать блокировок, используйте WAIT.

Также я не уверен, что DEFAULT constrain изменяет существующие строки. Вероятно, NOT NULL сдерживает использование вместе с DEFAULT в случае, описанном автором.

Если он изменит, добавьте его в конец Итак, псевдокод будет выглядеть так:

-- without NOT NULL constrain -- we will add it in the end
ALTER TABLE table ADD new_column INT DEFAULT 0

DECLARE fillNullColumn CURSOR LOCAL FAST_FORWARD
    SELECT 
        key
    FROM
        table WITH (NOLOCK)
    WHERE
        new_column IS NULL

OPEN fillNullColumn

DECLARE 
    @key INT

FETCH NEXT FROM fillNullColumn INTO @key

WHILE @@FETCH_STATUS = 0 BEGIN
     UPDATE
         table WITH (ROWLOCK)
     SET
         new_column = 0 -- default value
     WHERE
         key = @key

     WAIT 00:00:05 --wait 5 seconds, keep in mind it causes updating only 12 rows per minute

     FETCH NEXT FROM fillNullColumn INTO @key
END

CLOSE fillNullColumn
DEALLOCATE fillNullColumn

ALTER TABLE table ALTER COLUMN new_column ADD CONSTRAIN xxx

Я уверен, что есть некоторые синтаксические ошибки, но я надеюсь, что это помогите решить вашу проблему.

Удачи!

Ответ 12

Вертикально сегментируйте таблицу. Это означает, что у вас будет две таблицы с одним и тем же первичным ключом и точно такое же количество записей... Один из них будет у вас уже, у другого будет только ключ, а новый столбец Non-Null (с значение по умолчанию). Измените все вставки, обновление и удаление кода, чтобы они синхронизировали две таблицы... Если вы хотите, вы можете создать представление, которое "объединяет" две таблицы вместе, чтобы создать единую логическую комбинацию из двух, которая выглядит как одиночная таблица для клиента Выбор операторов...