Вопрос: Как добавить столбец в таблицу больших sql-серверов. Как в sql добавить столбец в таблицу
Как добавить столбец в таблицу больших sql-серверов
У меня есть таблица SQL Server в производстве, которая имеет миллионы строк, и оказывается, что мне нужно добавить к ней столбец. Или, если быть более точным, мне нужно добавить поле к сущности, которую представляет таблица.
Синтаксически это не проблема, и если в таблице не было так много строк и не было в производстве, это было бы легко.
На самом деле то, что мне нужно, - это курс действий. Существует множество веб-сайтов с чрезвычайно большими таблицами, и они должны время от времени добавлять поля. Как они делают это без существенного простоя?
Одна вещь, которую я должен добавить, я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.
Поэтому мне нужно выяснить, как добавить столбец со значением по умолчанию вовремя, или мне нужно выяснить способ обновления столбца позднее, а затем установить, что столбец не разрешает значения null.
20
2017-10-29 17:08
источник
Ответы:
ALTER TABLE table1 ADD newcolumn int NULL GOне займет много времени ... Что займет много времени, так это вставить столбцы в середине других столбцов ... b / c, тогда движок должен создать новую таблицу и скопировать данные в новую таблицу.
24
2017-10-29 17:15
Единственное реальное решение для непрерывного избыточность ,
Я признаю ответ @ Nestor, что добавление нового столбца не должно занять много времени в SQL Server, но, тем не менее, это все равно может быть отключением, которое неприемлемо для производственной системы. Альтернативой является изменение в параллельной системе, а затем, как только операция будет завершена, замените новое на старое.
Например, если вам нужно добавить столбец, вы можете создать копию таблицы, затем добавить столбец в эту копию, а затем использовать sp_rename() переместить старый стол в сторону и новую таблицу на место.
Если у вас есть ссылочные ограничения целостности, указывающие на эту таблицу, это может сделать обмен еще более сложным. Вероятно, вам придется кратко отказаться от ограничений при обмене таблицами.
Для некоторых видов сложных обновлений вы можете полностью дублировать базу данных на отдельном сервере. Как только это будет готово, просто замените записи DNS для двух серверов и voilà!
Я поддержал биржевую компанию в 1990-х годах, которые управляли тремя дубликатами серверов баз данных. Что чтобы они могли внедрять обновления на один сервер, сохраняя один производственный сервер и один переход на другой ресурс сервер. Их операции имели стандартная процедура вращения три машины через производство, отказоустойчивости и обслуживания каждый день , Когда им необходимо обновить оборудования, программного обеспечения или изменить схемы базы данных, потребовалось три дня распространять изменения через их серверов, но они могли бы это сделать без прерывание обслуживания. Все спасибо к избыточности.
12
2017-10-29 17:49
Msgstr "Добавить столбец, а затем выполнить относительно небольшие пакеты UPDATE, чтобы заполнить столбец значением по умолчанию. Это должно предотвратить любые заметные замедления"
И после этого вы должны установить столбец NOT NULL, который будет срабатывать в одной большой транзакции. Так что все будет работать очень быстро, пока вы это не сделаете, поэтому вы, вероятно, очень мало на самом деле получили. Я знаю это только из первых рук.
Возможно, вы захотите переименовать текущую таблицу с X на Y. Вы можете сделать это с помощью этой команды sp_RENAME '[OldTableName]', '[NewTableName]'.
Повторно создайте новую таблицу как X с новым столбцом, установленным в NOT NULL, а затем вставьте пакет из Y в X и включите значение по умолчанию либо в свою вставку для нового столбца, либо поместите значение по умолчанию в новый столбец при воссоздании таблицы X.
Я сделал этот тип изменений на столе с сотнями миллионов строк. Это продолжалось более часа, но это не привело к взрыву нашего журнала. Когда я попытался просто изменить столбец на NOT NULL со всеми данными в таблице, потребовалось более 20 часов, прежде чем я убил процесс.
Пробовали ли вы просто добавить столбец, заполняющий его данными, и установив столбец NOT NULL?
Поэтому, в конце концов, я не думаю, что есть волшебная пуля.
7
2017-10-29 17:52
Я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.
Добавление NOT NULL столбец с DEFAULT Ограничение таблицы любого количества строк (даже миллиардов) стало много более простой запуск в SQL Server 2012 (но только для Enterprise Edition), поскольку они позволили ему работать в Интернете (в большинстве случаев), где для существующих строк значение будет считано из метаданных и фактически не хранится в строке до тех пор, пока строка обновляется, или кластеризованный индекс перестраивается. Вместо того, чтобы перефразировать больше, вот соответствующий раздел со страницы MSDN для ALTER TABLE :
Добавление NOT NULL столбцов в качестве онлайн-операции
Начиная с SQL Server 2012 Enterprise Edition добавление столбца NOT NULL со значением по умолчанию - это онлайн-операция, когда значением по умолчанию является постоянная времени выполнения , Это означает, что операция выполняется почти мгновенно, независимо от количества строк в таблице. Это связано с тем, что существующие строки в таблице не обновляются во время операции; вместо этого значение по умолчанию сохраняется только в метаданных таблицы, и значение просматривается по мере необходимости в запросах, которые обращаются к этим строкам. Такое поведение происходит автоматически; дополнительный синтаксис не требуется для реализации онлайн-операции за синтаксисом ADD COLUMN. Константа времени выполнения - это выражение, которое производит одно и то же значение во время выполнения для каждой строки таблицы независимо от ее детерминизма. Например, константное выражение «Мои временные данные» или системная функция GETUTCDATETIME () являются константами времени выполнения. Напротив, функции NEWID () или NEWSEQUENTIALID () не являются константами времени выполнения, поскольку для каждой строки таблицы создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, который не является константой времени выполнения, всегда выполняется в автономном режиме, а эксклюзивная (SCH-M) блокировка выполняется на время операции.
Хотя существующие строки ссылаются на значение, хранящееся в метаданных, значение по умолчанию сохраняется в строке для любых вставленных новых строк и не указывает другое значение для столбца. Значение по умолчанию, хранящееся в метаданных, перемещается в существующую строку при обновлении строки (даже если фактический столбец не указан в инструкции UPDATE), или если таблица или кластерный индекс перестраиваются.
Колонны типа varchar (max), nvarchar (max), varbinary (max), xml, текст, ntext, изображение, иерархия, геометрия, география , или CLR UDTS, не могут быть добавлены в онлайн-операции. Столбец нельзя добавить в сеть, если это приводит к тому, что максимально возможный размер строки превышает предел в 8,060 байт. В этом случае колонка добавляется как автономная операция.
6
2017-08-17 20:00
выберите новую таблицу и переименуйте ее. Пример. Добавление столбца i в таблицу A:
select *, 1 as i into A_tmp from A_tbl //Add any indexes here exec sp_rename 'A_tbl', 'A_old' exec sp_rename 'A_tmp', 'A_tbl'Должно быть быстрым и не трогать журнал транзакций, как вставка в партии. (Я только что сделал это сегодня с таблицей из 70 миллионов строк за <2 мин).
Вы можете обернуть его в транзакцию, если вам нужно, чтобы он был онлайн-операцией (что-то может измениться в таблице между выбором и переименованием).
3
2017-10-29 18:09
Другой метод заключается в том, чтобы добавить столбец в новую связанную таблицу (предположим, что отношение «один к одному» вы можете обеспечить, предоставив FK уникальный индекс). Затем вы можете заполнить это партиями, а затем вы можете добавить соединение в эту таблицу, где бы вы ни хотели, чтобы данные отображались. Примечание. Я бы рассматривал это только для столбца, который я бы не хотел использовать в каждом запросе в исходной таблице, или если ширина записи моей исходной таблицы становилась слишком большой или добавлялась несколько столбцов.
0
2017-10-29 18:46
programmerz.ru
sql - SQL - Как добавить столбец на основе нескольких столбцов в таблице
Всем добрый день. Я не смог найти решение и искал какую-либо помощь. У меня есть таблица, для которой я хотел бы добавить столбец:
ID Time_Stamp Activity_Log 1254 6/1/2013 8:00:00AM 'Ticket was opened by user A' 1254 6/2/2013 8:20:00AM 'Comment was logged by user B' 1254 6/4/2013 8:25:00AM '**Ticket was transferred to Group 1**' 1254 6/7/2013 8:50:00AM 'Comment was logged by user B' 1254 6/11/2013 10:00:00AM 'Comment was logged by user C' 1254 6/12/2013 12:00:00PM '**Ticket was transferred to Group 2**' 7589 6/3/2013 11:00:00PM 'Ticket was opened by user A' 7589 6/5/2013 1:00:00PM 'Comment was logged by user C' 7589 6/7/2013 2:00:00PM 'Resolution provided by user C' 7589 6/11/2013 5:00:00PM 'Ticket resolved by user C' 8526 6/3/2013 7:00:00PM 'Ticket opened by user D' 8526 6/4/2013 2:00:00PM '**Ticket was transferred to Group 2**' 8526 6/5/2013 1:00:00PM 'Comment was logged by user G' 8526 6/8/2013 5:00:00PM '**Ticket was resolved by user C**'благодаря
источник поделитьсяqaru.site
Как добавить столбец в таблицу больших sql-серверов
У меня есть таблица SQL Server в производстве, которая имеет миллионы строк, и оказывается, что мне нужно добавить к ней столбец. Или, если быть более точным, мне нужно добавить поле к сущности, которую представляет таблица.
Синтаксически это не проблема, и если в таблице не было так много строк и не было в производстве, это было бы легко.
На самом деле то, что мне нужно, - это курс действий. Существует множество веб-сайтов с чрезвычайно большими таблицами, и они должны время от времени добавлять поля. Как они делают это без существенного простоя?
Одна вещь, которую я должен добавить, я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.
Поэтому мне нужно выяснить, как добавить столбец со значением по умолчанию вовремя, или мне нужно выяснить способ обновления столбца позднее, а затем установить, что столбец не разрешает значения null.
20
2017-10-29 17:08
источник
Ответы:
ALTER TABLE table1 ADD newcolumn int NULL GOне займет много времени ... Что займет много времени, так это вставить столбцы в середине других столбцов ... b / c, тогда движок должен создать новую таблицу и скопировать данные в новую таблицу.
24
2017-10-29 17:15
Единственное реальное решение для непрерывного избыточность ,
Я признаю ответ @ Nestor, что добавление нового столбца не должно занять много времени в SQL Server, но, тем не менее, это все равно может быть отключением, которое неприемлемо для производственной системы. Альтернативой является изменение в параллельной системе, а затем, как только операция будет завершена, замените новое на старое.
Например, если вам нужно добавить столбец, вы можете создать копию таблицы, затем добавить столбец в эту копию, а затем использовать sp_rename() переместить старый стол в сторону и новую таблицу на место.
Если у вас есть ссылочные ограничения целостности, указывающие на эту таблицу, это может сделать обмен еще более сложным. Вероятно, вам придется кратко отказаться от ограничений при обмене таблицами.
Для некоторых видов сложных обновлений вы можете полностью дублировать базу данных на отдельном сервере. Как только это будет готово, просто замените записи DNS для двух серверов и voilà!
Я поддержал биржевую компанию в 1990-х годах, которые управляли тремя дубликатами серверов баз данных. Что чтобы они могли внедрять обновления на один сервер, сохраняя один производственный сервер и один переход на другой ресурс сервер. Их операции имели стандартная процедура вращения три машины через производство, отказоустойчивости и обслуживания каждый день , Когда им необходимо обновить оборудования, программного обеспечения или изменить схемы базы данных, потребовалось три дня распространять изменения через их серверов, но они могли бы это сделать без прерывание обслуживания. Все спасибо к избыточности.
12
2017-10-29 17:49
Msgstr "Добавить столбец, а затем выполнить относительно небольшие пакеты UPDATE, чтобы заполнить столбец значением по умолчанию. Это должно предотвратить любые заметные замедления"
И после этого вы должны установить столбец NOT NULL, который будет срабатывать в одной большой транзакции. Так что все будет работать очень быстро, пока вы это не сделаете, поэтому вы, вероятно, очень мало на самом деле получили. Я знаю это только из первых рук.
Возможно, вы захотите переименовать текущую таблицу с X на Y. Вы можете сделать это с помощью этой команды sp_RENAME '[OldTableName]', '[NewTableName]'.
Повторно создайте новую таблицу как X с новым столбцом, установленным в NOT NULL, а затем вставьте пакет из Y в X и включите значение по умолчанию либо в свою вставку для нового столбца, либо поместите значение по умолчанию в новый столбец при воссоздании таблицы X.
Я сделал этот тип изменений на столе с сотнями миллионов строк. Это продолжалось более часа, но это не привело к взрыву нашего журнала. Когда я попытался просто изменить столбец на NOT NULL со всеми данными в таблице, потребовалось более 20 часов, прежде чем я убил процесс.
Пробовали ли вы просто добавить столбец, заполняющий его данными, и установив столбец NOT NULL?
Поэтому, в конце концов, я не думаю, что есть волшебная пуля.
7
2017-10-29 17:52
Я не хотел, чтобы столбец разрешал null, что означало бы, что мне нужно иметь значение по умолчанию.
Добавление NOT NULL столбец с DEFAULT Ограничение таблицы любого количества строк (даже миллиардов) стало много более простой запуск в SQL Server 2012 (но только для Enterprise Edition), поскольку они позволили ему работать в Интернете (в большинстве случаев), где для существующих строк значение будет считано из метаданных и фактически не хранится в строке до тех пор, пока строка обновляется, или кластеризованный индекс перестраивается. Вместо того, чтобы перефразировать больше, вот соответствующий раздел со страницы MSDN для ALTER TABLE :
Добавление NOT NULL столбцов в качестве онлайн-операции
Начиная с SQL Server 2012 Enterprise Edition добавление столбца NOT NULL со значением по умолчанию - это онлайн-операция, когда значением по умолчанию является постоянная времени выполнения , Это означает, что операция выполняется почти мгновенно, независимо от количества строк в таблице. Это связано с тем, что существующие строки в таблице не обновляются во время операции; вместо этого значение по умолчанию сохраняется только в метаданных таблицы, и значение просматривается по мере необходимости в запросах, которые обращаются к этим строкам. Такое поведение происходит автоматически; дополнительный синтаксис не требуется для реализации онлайн-операции за синтаксисом ADD COLUMN. Константа времени выполнения - это выражение, которое производит одно и то же значение во время выполнения для каждой строки таблицы независимо от ее детерминизма. Например, константное выражение «Мои временные данные» или системная функция GETUTCDATETIME () являются константами времени выполнения. Напротив, функции NEWID () или NEWSEQUENTIALID () не являются константами времени выполнения, поскольку для каждой строки таблицы создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, который не является константой времени выполнения, всегда выполняется в автономном режиме, а эксклюзивная (SCH-M) блокировка выполняется на время операции.
Хотя существующие строки ссылаются на значение, хранящееся в метаданных, значение по умолчанию сохраняется в строке для любых вставленных новых строк и не указывает другое значение для столбца. Значение по умолчанию, хранящееся в метаданных, перемещается в существующую строку при обновлении строки (даже если фактический столбец не указан в инструкции UPDATE), или если таблица или кластерный индекс перестраиваются.
Колонны типа varchar (max), nvarchar (max), varbinary (max), xml, текст, ntext, изображение, иерархия, геометрия, география , или CLR UDTS, не могут быть добавлены в онлайн-операции. Столбец нельзя добавить в сеть, если это приводит к тому, что максимально возможный размер строки превышает предел в 8,060 байт. В этом случае колонка добавляется как автономная операция.
6
2017-08-17 20:00
выберите новую таблицу и переименуйте ее. Пример. Добавление столбца i в таблицу A:
select *, 1 as i into A_tmp from A_tbl //Add any indexes here exec sp_rename 'A_tbl', 'A_old' exec sp_rename 'A_tmp', 'A_tbl'Должно быть быстрым и не трогать журнал транзакций, как вставка в партии. (Я только что сделал это сегодня с таблицей из 70 миллионов строк за <2 мин).
Вы можете обернуть его в транзакцию, если вам нужно, чтобы он был онлайн-операцией (что-то может измениться в таблице между выбором и переименованием).
3
2017-10-29 18:09
Другой метод заключается в том, чтобы добавить столбец в новую связанную таблицу (предположим, что отношение «один к одному» вы можете обеспечить, предоставив FK уникальный индекс). Затем вы можете заполнить это партиями, а затем вы можете добавить соединение в эту таблицу, где бы вы ни хотели, чтобы данные отображались. Примечание. Я бы рассматривал это только для столбца, который я бы не хотел использовать в каждом запросе в исходной таблице, или если ширина записи моей исходной таблицы становилась слишком большой или добавлялась несколько столбцов.
0
2017-10-29 18:46
programmerz.ru
