Как включить изменение в таблицы sql

Сообщение об ошибке «Сохранение изменений не разрешено» в SSMS

Эта статья поможет вам решить проблему, при которой при попытке сохранить таблицу в SQL Server Management Studio (SSMS) отображается сообщение об ошибке.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 956176

Симптомы

При попытке сохранить таблицу после внесения изменений в таблицу с помощью конструктора в SQL Server Management Studio может появиться следующее сообщение об ошибке:

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

Эта проблема возникает при внесении одного или нескольких из следующих изменений в таблицу:

• Вы изменяете параметр «Разрешить значения NULL» для столбца.
• Вы изменяете порядок столбцов в таблице.
• Вы изменяете тип данных столбца.
• Вы добавляете новый столбец.
• Вы изменяете filegroup таблицы или ее text/image данные.

Причина

Эта проблема возникает из-за того, что параметр Запретить сохранение изменений, требующих повторного создания таблицы включен по умолчанию в SQL Server Management Studio.

Когда вы изменяете таблицу таким образом, что изменяете структуру метаданных таблицы, а затем сохраняете таблицу, таблица должна быть создана повторно на основе этих изменений. Это может привести к потере метаданных и прямой потере данных во время повторного создания таблицы. Если включить параметр Запретить сохранение изменений, требующих повторного создания таблицы в разделе Конструктор окна Параметры Server Management Studio (SSMS) отобразится сообщение об ошибке, упомянутое в разделе «Симптомы».

Обходной путь

Чтобы обойти эту проблему, используйте операторы ALTER TABLE Transact-SQL для внесения изменений в структуру метаданных таблицы.

Например, чтобы изменить столбец MyDate типа datetime в таблице MyTable для принятия значений NULL, можно использовать:

alter table MyTable alter column MyDate7 datetime NULL 

Настоятельно рекомендуется не отключать параметр «Запретить сохранение изменений, требующих повторного создания таблицы», чтобы обойти эту проблему. Дополнительные сведения о рисках отключения этого параметра приведены в разделе «Дополнительные сведения».

Дополнительная информация

Чтобы изменить параметр Запретить сохранение изменений, требующих повторного создания таблицы, выполните следующие действия:

1. Откройте Среда SQL Server Management Studio.
2. В меню Сервис щелкните пункт Параметры.
3. В области навигации окна Параметры щелкните Конструкторы.
4. Установите или снимите флажок Запретить сохранение изменений, требующих повторного создания таблицы, затем нажмите кнопку ОК.

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

Риск отключения параметра «Запретить сохранение изменений, требующих повторного создания таблицы»

Хотя отключение этого параметра поможет избежать повторного создания таблицы, это также может привести к потере изменений. Например, предположим, что вы включаете функцию «Отслеживание изменений» в SQL Server для отслеживания изменений в таблице. При выполнении операции, которая вызывает повторное создание таблицы, отобразится сообщение об ошибке, упомянутое в разделе Симптомы. Однако если отключить этот параметр, существующие сведения об отслеживании изменений будут удалены при повторном создании таблицы. Поэтому мы рекомендуем не отключать этот параметр, чтобы обойти эту проблему.

Чтобы определить, включена ли функция «Отслеживание изменений» для таблицы, выполните следующие действия:

1. В SQL Server Management Studio найдите таблицу в обозревателе объектов.
2. Щелкните таблицу правой кнопкой мыши и выберите пункт «Свойства».
3. В диалоговом окне «Свойства таблицы» выберите «Отслеживание изменений». Если для элемента «Отслеживание изменений» задано значение True, этот параметр включен для таблицы. Если значение — False, этот параметр отключен.

Если функция Change Tracking включена, используйте операторы Transact-SQL, чтобы изменить структуру метаданных таблицы.

Действия по воспроизведению проблемы

1. В SQL Server Management Studio создайте таблицу, содержащую первичный ключ в конструкторе таблиц.
2. Щелкните правой кнопкой мыши базу данных,содержащую эту таблицу, и выберите пункт Свойства.
3. В диалоговом окне Свойства базы данных нажмите Отслеживание изменений.
4. Задайте для элемента Отслеживание изменений значение True и нажмите кнопку ОК.
5. Щелкните таблицу правой кнопкой мыши и выберите пункт Свойства.
6. В диалоговом окне Свойства таблицы нажмите Отслеживание изменений.
7. Задайте для элемента Отслеживание изменений значение True и нажмите кнопку ОК.
8. В меню Сервис щелкните пункт Параметры.
9. В диалоговом окне Параметры выберите Конструкторы.
10. Установите флажок Запретить сохранение изменений, требующих повторного создания таблицы и нажмите кнопку ОК.
11. В конструкторе таблиц измените параметр Разрешить значения NULL для существующего столбца.
12. Попробуйте сохранить изменение в таблице.

Обратная связь

Были ли сведения на этой странице полезными?

Включение и отключение отслеживания изменений (SQL Server)

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

Включение отслеживания изменений для базы данных

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

ALTER DATABASE AdventureWorks2022 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) 

Включить отслеживание изменений можно также в SQL Server Management Studio в диалоговом окне Свойства базы данных (страница «Отслеживание изменений»). Если база данных содержит оптимизированные для памяти таблицы, включить отслеживание изменений с помощью SQL Server Management Studio невозможно. Чтобы включить отслеживание изменений, используйте T-SQL.

При включении отслеживания изменений, а также в любое время в дальнейшем можно указать и изменить значения параметров CHANGE_RETENTION и AUTO_CLEANUP.

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

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

При этом следует учесть следующие моменты.

• При отслеживании изменений уровень совместимости базы данных должен быть не ниже 90. Если уровень совместимости базы данных менее 90, то можно настроить отслеживание изменений. Однако функция CHANGETABLE, используемая для получения сведений об отслеживании изменений, возвратит ошибку.
• Простейший способ обеспечения согласованности всех данных отслеживания изменений — изоляция моментальных снимков. По этой причине настоятельно рекомендуется включить для базы данных изоляцию моментальных снимков. Дополнительные сведения см. в статье «Работа с отслеживанием изменений» (SQL Server).

Включение отслеживания изменений для таблицы

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

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

ALTER TABLE Person.Contact ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) 

Включить отслеживание изменений для таблицы можно также в SQL Server Management Studio в диалоговом окне Свойства базы данных (страница «Отслеживание изменений»).

Если параметр TRACK_COLUMNS_UPDATED имеет значение ON, ядро СУБД SQL Server сохраняет дополнительные сведения о том, какие столбцы были обновлены в внутренней таблице отслеживания изменений. Отслеживание столбцов позволяет приложению синхронизировать только те столбцы, которые были обновлены. Это может повысить эффективность и производительность. Но поскольку отслеживание столбцов требует дополнительного места на диске, по умолчанию этот параметр имеет значение OFF.

Отключение отслеживания изменений для таблицы или базы данных

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

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

ALTER TABLE Person.Contact DISABLE CHANGE_TRACKING; 

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

ALTER DATABASE AdventureWorks2022 SET CHANGE_TRACKING = OFF 

C# Отслеживание изменений данных в таблице MSSQL

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

Отслеживать
799 5 5 серебряных знаков 13 13 бронзовых знаков
задан 26 авг 2017 в 19:43
1,629 1 1 золотой знак 13 13 серебряных знаков 37 37 бронзовых знаков
Посмотрите это msdn.microsoft.com/ru-ru/library/62xk7953(v=vs.110).aspx
26 авг 2017 в 19:50

matrix, интересно, но он же всё ровно будет делать SELECT по таймеру и смотреть? Это же чисто ради удобства? Или задействован какой-то новый функционал SQL, просто не слышал раньше о таком.

28 авг 2017 в 9:05

@xSx, здесь будет использовать механизм внутренних событий СУБД. Функционал не новый, появился еще в SQL Server 2005.

29 авг 2017 в 10:41

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Можно использовать класс SqlDependency.

1) Необходимо убедиться, что для целевой БД включен Service Broker

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database_name'; 

2) Если Service Broker не включен, то нужно включить его. При этом не должно быть активных соединений с БД

ALTER DATABASE [Database_name] SET ENABLE_BROKER; 

3) Пример C# приложения с MSDN

void Initialization() < // Create a dependency connection. SqlDependency.Start(connectionString, queueName); >void SomeMethod() < // Assume connection is an open SqlConnection. // Create a new SqlCommand object. using (SqlCommand command=new SqlCommand( "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection)) < // Create a dependency and associate it with the SqlCommand. SqlDependency dependency=new SqlDependency(command); // Maintain the refence in a class member. // Subscribe to the SqlDependency event. dependency.OnChange+=new OnChangeEventHandler(OnDependencyChange); // Execute the command. using (SqlDataReader reader = command.ExecuteReader()) < // Process the DataReader. >> > // Handler method void OnDependencyChange(object sender, SqlNotificationEventArgs e ) < // Handle the event (for example, invalidate this cache entry). >void Termination() < // Release the dependency. SqlDependency.Stop(connectionString, queueName); >

В объекте SqlCommand после ключевого слова select в запросе перечислены поля таблиц БД, изменения значений которых необходимо отслеживать. Также важно, чтобы была явно указана схема (по умолчанию dbo ).

При возникновении события вызывается метод OnDependencyChange .

Пара слов про Alter Table, или как делать не надо

Это скорее не статья, а небольшая заметка о некоторых особенностях работы с большими таблицами в MySQL.

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

Итак, как-то вечерком, дабы не тревожить наших дорогих заказчиков, понадобилось нам добавить колонку в таблицу.

Чтобы было понятнее, характеристики таблицы и базы:

• размер таблицы 110Gb
• число строк: 7.5 млн
• storage engine: InnoDB
• есть два sql-сервера, соединенных по схеме master-slave, при этом master — на SSD, а slave — на HDD

alter table table_name add source varchar(32)

Им мы и воспользовались (да, мы понимали, что это плохо, но в данном конкретном случае риски были минимальны).

Результаты оказались довольно неприятными:

• на мастере процесс добавления колонки шел около часа (!)
• на слейве он начался после окончания процесса на мастере и продолжался около 8 часов (!!)
• во время выполнения alter table на слейве полностью остановилась репликация данных (. )

На графиках ниже это наглядно видно.

График загрузки CPU на мастере.

График загрузки CPU на слейве.

Отставание репликации.

Какие неприятности ждут тех, кто делает это на боевых таблицах?

Во-первых, на время выполнения Alter Table нельзя писать данные в таблицу (но можно читать). На самом деле это зависит от версии MySQL, в последних это не так, но тем не менее надо понимать, на что способна именно Ваша версия, дабы избежать неприятностей.

Соответственно, если таблица большая, то время недоступности будет значительным (как у нас, при использовании SSD это заняло час, а на обычном диске — 8 часов), что вряд ли ожидают Ваши заказчики.

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

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

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

Кроме того, во время выполнения всяких Alter Table все изменения записываются в лог-файл, чтобы после изменений накатить данные за то время, в течение которого проводилась операция. И тут тоже может ждать неприятный сюрприз: если таблица изменяется долго, а объем операций большой, то может закончится не только место на диске, но и превыситься лимит на размер файла, указанный в настройках SQL. В любом случае Вас ожидает «the online DDL operation fails, and uncommitted concurrent DML operations are rolled back».

Мы столкнулись с тем, что каталог для временных файлов был маловат, в результате пришлось переопределить innodb_tmpdir.

Посмотреть, куда указывает переменная в данный момент, можно так:

select @@GLOBAL.innodb_tmpdir;

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

А как же делать надо? На самом деле нет единого рецепта на все случаи жизни.

Один из возможных вариантов, как делаем мы для таблиц, которые не критичны на обновление:

• Создаем новую таблицу с нужной структурой
• Заполняем поля из старой таблицы
• Удаляем или переименовываем старую таблицу
• Переименовываем новую

UPD. Пользователь syavadee посоветовал использовать percona online schema change. По сути она реализует описанный выше алгоритм с дополнительными плюшками.

UPD. Пользователь arheops рекомендует включить parallel replication/gtid для решения проблем с репликацией.

Ну и попутно, иногда, чтобы понять, насколько большая таблица и сколько в ней строк, нужно, как учат, сделать

select count(*) from table_name

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

Поэтому для примерной оценки объема можно воспользоваться следующим способом:

SHOW TABLE STATUS FROM express where name='table_name'

К сожалению, на движке InnoDB полученный размер может отличаться процентов на 50 (в нашем случае с таблицей выше реальное число записей порядка 7.5 млн, а указанный способ показал только 5 млн), но для ориентировочной оценки это вполне подходит.

На этом все, надеюсь, заметка кому-то поможет избежать больших неприятностей с якобы безобидными командами SQL.

• Блог компании SRG
• MySQL
• Администрирование баз данных