My<cod/>.net
Проверьте наличие изменений в таблице сервера SQL?
Как я могу контролировать базу данных сервера SQL на предмет изменений в таблице без использования триггеров или изменения структуры базы данных каким-либо образом? Моя предпочтительная среда программирования-это .NET и C#.
Я хотел бы иметь возможность поддерживать любой SQL Server 2000 SP4 или новее. Мое приложение - это простая визуализация данных для продукта другой компании. Наша клиентская база исчисляется тысячами, поэтому я не хочу вводить требования, чтобы мы изменяли таблицу сторонних поставщиков при каждой установке.
Под "changes to a table" я подразумеваю изменения в табличных данных, а не изменения в структуре таблицы.
В конечном счете, я хотел бы, чтобы изменение инициировало событие в моем приложении, вместо того, чтобы проверять изменения через определенный промежуток времени.
Лучший способ действия, учитывая мои требования (никаких триггеров или модификаций схемы, SQL Server 2000 и 2005), по-видимому, заключается в использовании функции BINARY_CHECKSUM в T-SQL . Вот как я планирую это осуществить:
Каждые X секунд выполняйте следующий запрос:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);
И сравните это с сохраненным значением. Если значение изменилось, пройдите по строкам таблицы с помощью запроса:
SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);
И сравните возвращенные контрольные суммы с сохраненными значениями.
Взгляните на команду CHECKSUM:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);
Это будет возвращать один и тот же номер при каждом запуске, если содержимое таблицы не изменилось. Смотрите мой пост об этом для получения дополнительной информации:
Вот как я использовал его для восстановления зависимостей кэша при изменении таблиц:
ASP.NET 1.1 зависимость кэша базы данных (без триггеров)
К сожалению, CHECKSUM не всегда работает должным образом, чтобы обнаружить изменения .
Это всего лишь примитивная контрольная сумма и никакого вычисления циклической проверки избыточности (CRC).
Поэтому вы не можете использовать его для обнаружения всех изменений, например, симметричные изменения приводят к одному и тому же CHECKSUM!
Например, решение с CHECKSUM_AGG(BINARY_CHECKSUM(*)) всегда будет давать 0 для всех 3 таблиц с различным содержанием:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM
(
SELECT 1 as numA, 1 as numB
UNION ALL
SELECT 1 as numA, 1 as numB
) q
-- delivers 0!SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( SELECT 1 as numA, 2 as numB UNION ALL SELECT 1 as numA, 2 as numB ) q -- delivers 0!
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ( SELECT 0 as numA, 0 as numB UNION ALL SELECT 0 as numA, 0 as numB ) q -- delivers 0!
Почему вы не хотите использовать триггеры? Они хороши, если вы используете их правильно. Если вы используете их в качестве способа обеспечения ссылочной целостности, то это происходит тогда, когда они переходят от хорошего к плохому. Но если вы используете их для мониторинга, они на самом деле не считаются табу.
Как часто вам нужно проверять наличие изменений и насколько велики (с точки зрения размера строки) таблицы в базе данных? Если вы используете метод CHECKSUM_AGG(BINARY_CHECKSUM(*)) , предложенный Джоном, он будет сканировать каждую строку указанной таблицы. Подсказка NOLOCK помогает, Но в большой базе данных вы все равно попадаете в каждую строку. Вам также нужно будет сохранить контрольную сумму для каждой строки, чтобы вы сказали, что она изменилась.
Вы рассматривали возможность взглянуть на это с другой стороны? Если вы не хотите изменять схему для добавления триггеров (что имеет смысл, поскольку это не ваша база данных), вы рассматривали возможность работы с поставщиком приложения, который создает базу данных?
Они могут реализовать функцию API, которая обеспечивает механизм уведомления вспомогательных приложений об изменении данных. Это может быть так же просто, как запись в таблицу уведомлений, в которой перечислены, какая таблица и какая строка были изменены. Это может быть реализовано с помощью триггеров или кода приложения. С вашей стороны, ти не будет иметь значения, ваша единственная забота будет сканировать таблицу уведомлений на периодической основе. Снижение производительности базы данных было бы намного меньше, чем сканирование каждой строки на предмет изменений.
Самым сложным было бы убедить поставщика приложения реализовать эту функцию. Поскольку это может быть полностью обработано через триггеры SQL via, вы можете выполнить основную часть работы для них, написав и протестировав триггеры, а затем принести код поставщику приложения. Если поставщик поддерживает триггеры, это предотвращает ситуацию, когда добавление триггера непреднамеренно заменяет триггер, поставленный поставщиком.
К сожалению, я не думаю, что есть чистый способ сделать это в SQL2000. Если вы сузите свои требования до SQL Server 2005 (и более поздних версий), то вы находитесь в бизнесе. Вы можете использовать класс SQLDependency в System.Data.SqlClient . См. раздел Уведомления о запросах на сервере SQL (ADO.NET).
Есть задание DTS (или задание, которое запускается службой windows), которое выполняется с заданным интервалом. При каждом запуске он получает информацию о данной таблице с помощью системных таблиц INFORMATION_SCHEMA и записывает эти данные в хранилище данных. Сравните данные, возвращенные относительно структуры таблицы, с данными, возвращенными в предыдущий раз. Если она отличается, тогда вы знаете, что структура изменилась.
Пример запроса для возврата информации относительно всех столбцов в таблице ABC (в идеале перечислите только столбцы из таблицы INFORMATION_SCHEMA, которые вы хотите, вместо использования *select **, как я делаю здесь):
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'
Вы будете отслеживать различные столбцы и представления INFORMATION_SCHEMA в зависимости от того, как именно вы определяете "changes to a table".