Вопрос по sql, sql-server, sql-server-2008, sql-server-2008-r2 – Изменить типы столбцов в огромной таблице

18

У меня есть таблица в SQL Server 2008 R2 с около миллиарда строк. Я хочу изменить тип данных двух столбцов с int на bigint. Два разаALTER TABLE zzz ALTER COLUMN yyy работает, но очень медленно. Как я могу ускорить процесс? Я думал скопировать данные в другую таблицу, удалить, создать, скопировать обратно и переключиться в простой режим восстановления или каким-то образом сделать это с курсором по 1000 строк за раз, но я не уверен, приведет ли это на самом деле к какому-либо улучшению.

Можете ли вы объяснить, что вы меняете тип данных с и на? Aaron Bertrand
я думаюAlter Предоставленное вами заявление - ваш лучший выбор здесь. mattytommo
Что-то вроде создания нового столбца, копирования данных в курсоре, удаления старого столбца, переименования нового столбца. Понятия не имею, работает ли это. user1417408
Я не вижу, как вы можете использовать курсор для изменения типа столбца? Тип столбца - это изменениеall значения в таблице. Oded
целое число в bigints user1417408

Ваш Ответ

2   ответа
29

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

drop any indexes/constraints pointing to the old column, and disable triggers add a new nullable column with the new data type (even if it is meant to be NOT NULL) update the new column setting it equal to the old column's value (and you can do this in chunks of individual transactions (say, affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log) once the updates are all done, drop the old column rename the new column (and add a NOT NULL constraint if appropriate) rebuild indexes and update statistics

Ключевым моментом здесь является то, что он позволяет выполнять обновление постепенно на шаге 3, что вы не можете сделать с помощью одной команды ALTER TABLE.

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

EDIT

Кроме того, и просто удивляюсь вслух, я не проводил никакого тестирования для этого (но добавлял его в список). Интересно, поможет ли здесь сжатие строк + строк? Если вы измените INT на BIGINT, со сжатием на месте SQL Server все равно будет обрабатывать все значения, как если бы они все еще помещались в INT. Опять же, я не проверял, сделает ли это изменение быстрее или медленнее, или сколько времени потребуется, чтобы сначала добавить сжатие. Просто выбрасываю это туда.

@ user1417408 Хорошо, если исходный столбец обнуляем, тоWHERE newcol IS NULL AND oldcol IS NOT NULL...
БД будет в автономном режиме. Поможет ли настройка модели восстановления на простую? user1417408
Аарон, спасибо за вашу помощь. Скорость имеет значение, потому что сайт, использующий эту БД, будет недоступен только в течение нескольких часов. user1417408
@Gordon ALTER TABLE ... ДОБАВИТЬ обнуляемый столбец должен быть операцией только для метаданных и должен быть очень быстрым - я бы не квалифицировал это на том же уровне, что и "прохождение через таблицу". Также, если вы копируете всю таблицу, вы перемещаете намного больше данных, чем просто столбец int. Изменение таблиц также может быть очень сложным, если у вас есть ограничения, внешние ключи, это может испортить зависимости и т. Д. Я не думаю, что это принесет вам много пользы при работе в существующей таблице.
В режиме SIMPLE или FULL ваша огромная команда ALTER TABLE не будет отличаться, так как все записывается в обоих случаях. С учетом вышесказанного SIMPLE может быть немного лучше, но модель восстановления не должна иметь значения, если вы выбираете подходящий размер пакета. Также, если база данных «автономна» (Я предполагаю, что вы не имеете в виду ALTER DATABASE ... SET OFFLINE), почему скорость имеет значение? Вышеуказанное, вероятно, уменьшит процент времени, но это не будет невероятно быстрым. То, что вы делаете, просто требует времени.
-3

просто перейдите к таблице в базе данных, щелкните правой кнопкой мыши и выберите «Дизайн». и затем выберите столбец, который вы хотите редактировать: установите его на bigint и нажмите «Сохранить». Изменяет весь столбец, но предыдущие значения останутся без изменений. Это хорошо для того, чтобы позволить столу «вырасти из». int в bigint, но, насколько я знаю, не изменит существующие данные.

Похожие вопросы