Проверить наличие изменений в таблице SQL Server?
Как можно отслеживать изменения в таблице базы данных SQL Server, не используя триггеры и не изменяя структуру базы данных каким-либо образом? Моя предпочтительная среда программирования - .NET и C#.
Я хотел бы иметь возможность поддерживать любой SQL Server 2000 SP4 или новее. Мое приложение - это визуализация данных для продукта другой компании. Наша клиентская база исчисляется тысячами, поэтому я не хочу предъявлять требования, чтобы мы изменяли таблицу сторонних поставщиков при каждой установке.
Под "изменениями в таблице" я подразумеваю изменения в данных таблицы, а не изменения в структуре таблицы.
В конечном счете, я хотел бы, чтобы изменение вызвало событие в моем приложении, вместо того, чтобы проверять изменения с интервалом.
Наилучшим способом действий с учетом моих требований (без триггеров или изменений схемы, 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);
И сравните возвращенные контрольные суммы с сохраненными значениями.
9 ответов
Посмотрите на команду CHECKSUM:
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);
Он будет возвращать одно и то же число при каждом запуске, если содержимое таблицы не изменилось. Смотрите мой пост для получения дополнительной информации:
Вот как я использовал его для перестройки зависимостей кеша при изменении таблиц:
Зависимость кэша базы данных ASP.NET 1.1 (без триггеров)
К сожалению, CHECKSUM не всегда работает правильно, чтобы обнаружить изменения. Это только примитивная контрольная сумма и не вычисляется CRC. Поэтому вы не можете использовать его для обнаружения всех изменений, например, симметричные изменения приводят к одной и той же контрольной сумме!
Например решение с 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 с помощью триггеров, вы можете выполнить большую часть работы за них, написав и протестировав триггеры, а затем передав код поставщику приложений. Наличие у поставщика поддержки триггеров предотвращает ситуацию, когда добавление триггера непреднамеренно заменяет триггер, предоставленный поставщиком.
К сожалению, я не думаю, что есть чистый способ сделать это в SQL2000. Если вы сузите свои требования к SQL Server 2005 (и более поздним версиям), то вы в деле. Вы можете использовать SQLDependency класс в System.Data.SqlClient, См. Уведомления о запросах в SQL Server (ADO.NET).
Иметь задание DTS (или задание, запускаемое службой Windows), которое выполняется с заданным интервалом. Каждый раз, когда он запускается, он получает информацию о данной таблице с помощью системных таблиц INFORMATION_SCHEMA и записывает эти данные в хранилище данных. Сравните данные, возвращенные относительно структуры таблицы, с данными, возвращенными в предыдущий раз. Если это отличается, то вы знаете, что структура изменилась.
Пример запроса для получения информации обо всех столбцах таблицы ABC (в идеале перечисляются только те столбцы из таблицы INFORMATION_SCHEMA, которые вам нужны, вместо использования *select **, как здесь):
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'
Вы будете отслеживать различные столбцы и представления INFORMATION_SCHEMA в зависимости от того, как именно вы определяете "изменения в таблице".
Непростая догадка: если вы не хотите изменять сторонние таблицы, можете ли вы создать представление и затем включить триггер для этого представления?
Проверьте дату последнего коммита. Каждая база данных имеет историю, когда каждый коммит сделан. Я считаю, что это стандарт соответствия ACID.
SQL Server имеет представления, которые отслеживают внутренние изменения. Существуют столбцы, указывающие, когда произошли изменения, а также когда произошли поиски и поиски, подробности см. в sys-dm-db-index-usage-stats.
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Your DB Name')
ORDER BY last_user_update DESC
Предостережения:
- Эта информация сбрасывается каждый раз при перезапуске SQL Server.
- Вам понадобится ПРОСМОТР СОСТОЯНИЯ СЕРВЕРА, чтобы просмотреть sys.dm_db_index_usage_stats.
- Это работает для всего, что связано с индексами (большинство таблиц имеют кластеризованный индекс). Насчет кучи не уверен, я их не проверял.
