Поле SQL Server ALTER NOT NULL занимает вечность

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

CREATE TABLE Test
(
    T0 INT Not NULL,
    T1 INT NUll 
)

INSERT INTO Test VALUES(1, NULL) -- Works!

ALTER TABLE Test
    WITH NOCHECK
        ADD CONSTRAINT N_null_test CHECK (T1 IS NOT NULL)

    ALTER COLUMN T1 int NOT NULL 

INSERT INTO Test VALUES(1, NULL) -- Doesn't work now!

На самом деле у вас есть два варианта (добавлен третий, см. редактирование):

1. Используйте ограничение, которое предотвратит обновление любых новых строк и оставит исходные без изменений.
2. Обновите строки, которые являются нулевыми, на что-то другое, а затем примените параметр not null alter. Это действительно должно выполняться в нерабочее время, если вы не возражаете против того, чтобы процессы были заблокированы вне таблицы.

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

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

1. Пройтись по каждому ряду
2. Проверьте, является ли он нулевым
3. Обновите его соответствующим образом. Это займет некоторое время, но не заблокирует всю таблицу, чтобы другие программы не могли получить к ней доступ. (Не забудьте таблицу with(rowlock) намекать!)

EDIT: я только что подумал о третьем варианте: вы можете создать новую таблицу с соответствующими столбцами, а затем экспортировать данные из исходной таблицы в новую. Когда это будет сделано, вы можете удалить исходную таблицу и изменить имя новой на старое. Чтобы сделать это, вам придется отключить зависимости от оригинала и установить их обратно на новый, когда вы закончите, но этот процесс значительно сократит объем работы, которую вы должны выполнять в нерабочее время. Это тот же подход, который использует сервер sql, когда вы вносите изменения в порядок столбцов в таблицах через студию управления. Для этого подхода я бы сделал вставку по частям, чтобы убедиться, что вы не вызываете отмену нагрузки на систему и не мешаете другим получить к ней доступ. Затем в нерабочее время вы можете удалить оригинал, переименовать второй и применить зависимости и т. д. У вас все еще будет некоторая работа в нерабочее время, но она будет незначительной по сравнению с другим подходом.

Ссылка на использование sp_rename.

Единственный известный мне способ сделать это "быстро" (*) - это

• создание «теневой» таблицы с требуемым макетом
• добавление триггера в исходную таблицу, чтобы любые операции вставки/обновления/удаления копировались в теневую таблицу (обратите внимание на любые NULL, которые могут появиться!)
• скопируйте все данные из источника в теневую таблицу, возможно, небольшими порциями (убедитесь, что вы можете обрабатывать уже скопированные данные с помощью триггера(ов), убедитесь, что данные поместятся в новую структуру (ISNULL(?) ! )
• скрипт из всех зависимостей от/к другим таблицам
• when all is done, do the following inside an explicit transaction :
  • get an exclusive table lock on the source-table and one on the shadowtable
  • запустите скрипты, чтобы сбросить зависимости в исходную таблицу
  • переименуйте исходную таблицу во что-то другое (например, суффикс _old)
  • переименуйте теневую таблицу в исходное имя исходной таблицы
  • запустите скрипты, чтобы снова создать все зависимости

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

Как всегда, лучше сначала сделать пробный запуск на тестовом сервере =)

PS: пожалуйста, не поддавайтесь искушению воссоздать FK с помощью NOCHECK, это делает их бесполезными, поскольку оптимизатор не будет им доверять и не будет учитывать их при построении плана запроса.

(*: где быстро сводится к: с наименьшим возможным временем простоя)

Извините за беспокойство, но:

• Любые способы ускорить это: Нет, если вы хотите изменить саму структуру таблицы
• или я застрял, просто делая это ночью в нерабочее время? Да, и это, вероятно, к лучшему, как указал @HLGEM
• Также может ли это вызвать блокировку таблицы? да

Не имеет прямого отношения к вам (потому что речь идет о переходе от NOT NULL к NULL), но интересно прочитать по этой теме: http://beyondrelational.com/blogs/sankarreddy/archive/2011/04/05/is-alter-table-alter-column-not-null-to-null-always-expensive.aspx

И, наконец, немного древней истории - по эквивалентному вопросу на форуме в 2005 году было сделано то же предложение, что и @Kevin, предложенное выше, - использование ограничения вместо того, чтобы сделать сам столбец необнуляемым: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=50671