CHANGE TRACKING GET CHANGES: примеры (SQL)

Простые примеры использования

Пример получения изменений с использованием базовых параметров.

-- Включение отслеживания изменений для базы данных и таблицы
ALTER DATABASE MyDatabase
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE dbo.Products
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

-- Получение текущей версии
DECLARE @current_version bigint;
SET @current_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Первоначальная синхронизация (предположим, версия 0)
DECLARE @last_sync_version bigint = 0;

-- Запрос изменений для таблицы Products
SELECT *
FROM CHANGETABLE(CHANGES dbo.Products, @last_sync_version) AS CT;
-- В первый вызов вернутся все текущие записи в таблице как операции вставки.

SYS_CHANGE_VERSION | SYS_CHANGE_OPERATION | ProductID (PK)
-------------------|----------------------|---------------
1                  | I                    | 1
1                  | I                    | 2
1                  | I                    | 3

Пример с указанием флага для получения информации о столбцах.

DECLARE @last_version bigint = 5;
SELECT
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_OPERATION,
    CT.ProductID,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(
        COLUMNPROPERTY(OBJECT_ID('dbo.Products'), 'Price', 'ColumnId'),
        CT.SYS_CHANGE_COLUMNS) AS IsPriceChanged
FROM CHANGETABLE(CHANGES dbo.Products, @last_version) AS CT
WHERE CT.SYS_CHANGE_OPERATION = 'U';

SYS_CHANGE_VERSION | SYS_CHANGE_OPERATION | ProductID | IsPriceChanged
-------------------|----------------------|-----------|----------------
7                  | U                    | 2         | 1
8                  | U                    | 3         | 0

Похожие механизмы в MS SQL

В MS SQL Server существуют и другие технологии для отслеживания изменений данных, каждая со своей областью применения.

• Temporal Tables (Системные версионные таблицы): Автоматически сохраняют историю всех изменений данных в отдельной истории таблицы. Позволяют запрашивать состояние данных на любой момент времени. Предпочтительны для аудита и хранения полной истории, а не только факта изменения.
• Change Data Capture (CDC): Захватывает изменения на уровне журнала транзакций и сохраняет их в удобном табличном формате. Обеспечивает более детальную информацию, включая старые и новые значения. Требует больше ресурсов, чем отслеживание изменений, но предоставляет полные данные об изменениях. Часто используется в сложных ETL-процессах.
• Репликация: Механизм распространения данных между серверами. Отслеживает изменения для целей репликации, но это внутренний механизм, не предназначенный для прямого запроса приложениями.

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

Аналоги в других СУБД

Другие системы управления базами данных предлагают различные подходы к отслеживанию изменений.

PostgreSQL: Логическая репликация и механизм Logical Decoding позволяют получать поток изменений из Write-Ahead Log (WAL). Также можно использовать триггеры для создания таблицы изменений.

-- Пример с триггером (упрощенно)
CREATE TABLE audit_changes (
    id serial PRIMARY KEY,
    table_name text,
    operation char(1),
    old_data jsonb,
    new_data jsonb,
    change_time timestamp DEFAULT now()
);

-- Создание триггерной функции и триггера на таблицу.

Oracle: Oracle GoldenGate, Oracle Streams или Oracle CDC являются комплексными решениями для захвата изменений данных. Прямого аналога простой функции нет.

MySQL: Библиотека MySQL Binlog позволяет читать бинарный лог для отслеживания изменений. Также можно использовать триггеры.

-- Использование бинарного лога через командную строку
mysqlbinlog --database=my_db --start-datetime="2023-11-01 00:00:00" binlog.000001

SQLite: Не имеет встроенных механизмов отслеживания изменений. Реализуется через триггеры, создающие записи в отдельной таблице журнала.

Ключевое отличие MS SQL CHANGE_TRACKING — это встроенная, легковесная, версионная система на уровне ядра СУБД, не требующая создания дополнительных объектов вручную для базовой функциональности.

Типичные ошибки и проблемы

Ошибки часто возникают из-за непонимания модели версий и управления параметрами отслеживания.

1. Использование устаревшего или неверного номера версии. Если передать значение @last_sync_version больше текущей версии изменения, функция вернет пустой набор.

DECLARE @future_version bigint = 999999;
SELECT * FROM CHANGETABLE(CHANGES dbo.Products, @future_version) AS CT;
-- Результат: пустой набор строк.

-- (нет данных)

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

DECLARE @very_old_version bigint = 1;
-- Если изменения для версии 1 уже очищены, произойдет ошибка.
SELECT * FROM CHANGETABLE(CHANGES dbo.Products, @very_old_version) AS CT;

Сообщение 530: Ошибка истечения срока хранения данных отслеживания изменений.

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

SELECT * FROM CHANGETABLE(CHANGES dbo.NonTrackedTable, 0) AS CT;

Сообщение 52833: Для объекта "NonTrackedTable" не включено отслеживание изменений.

История изменений функции

Основная функциональность функции CHANGETABLE (используется совместно с CHANGE_TRACKING_GET_CHANGES) была представлена в SQL Server 2008 и с тех пор остается стабильной. Существенных изменений в синтаксисе или поведении в последних версиях SQL Server (2016, 2017, 2019, 2022) не было.

Основные улучшения в области отслеживания изменений были сосредоточены вокруг производительности, управления и интеграции с другими компонентами, такими как Always On Availability Groups. Например, улучшена работа отслеживания изменений во вторичных репликах групп доступности.

Важно отметить, что Microsoft продолжает поддерживать и развивать эту технологию как часть платформы данных, поэтому рекомендуется всегда использовать последние накопительные обновления для вашей версии SQL Server для получения исправлений ошибок.

Расширенные сценарии применения

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

CREATE TABLE dbo.SyncMetadata (
    TableName sysname PRIMARY KEY,
    LastSyncVersion bigint NOT NULL
);

-- Инициализация
INSERT INTO dbo.SyncMetadata (TableName, LastSyncVersion)
VALUES ('Products', CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Products')));

-- Процедура получения следующего пакета изменений
DECLARE @last_version bigint, @current_version bigint;
SELECT @last_version = LastSyncVersion FROM dbo.SyncMetadata WHERE TableName = 'Products';
SET @current_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Получение изменений
SELECT
    CT.SYS_CHANGE_OPERATION,
    CT.ProductID,
    P.* -- Получение актуальных данных (кроме удалений)
FROM CHANGETABLE(CHANGES dbo.Products, @last_version) AS CT
LEFT JOIN dbo.Products AS P ON CT.ProductID = P.ProductID
WHERE CT.SYS_CHANGE_VERSION <= @current_version
ORDER BY CT.SYS_CHANGE_VERSION;

-- Обновление версии после успешной обработки пакета
UPDATE dbo.SyncMetadata
SET LastSyncVersion = @current_version
WHERE TableName = 'Products';

2. Обработка операций удаления. Поскольку при удалении строки из основной таблицы соединиться с ней по LEFT JOIN не получится, важно обрабатывать операции 'D' отдельно.

DECLARE @last_sync bigint = 10;
SELECT
    CT.SYS_CHANGE_OPERATION,
    CT.ProductID
FROM CHANGETABLE(CHANGES dbo.Products, @last_sync) AS CT
WHERE CT.SYS_CHANGE_OPERATION = 'D';
-- Эта выборка вернет только ключи удаленных записей, которые нужно удалить в целевой системе.

3. Фильтрация изменений по конкретным столбцам. Использование функции CHANGE_TRACKING_IS_COLUMN_IN_MASK для реакций только на значимые изменения.

DECLARE @ColId int = COLUMNPROPERTY(OBJECT_ID('dbo.Products'), 'StockCount', 'ColumnId');
DECLARE @last_sync bigint = 50;

SELECT
    CT.ProductID,
    P.Name,
    P.StockCount
FROM CHANGETABLE(CHANGES dbo.Products, @last_sync) AS CT
INNER JOIN dbo.Products AS P ON CT.ProductID = P.ProductID
WHERE CT.SYS_CHANGE_OPERATION IN ('I','U')
    AND (
        CT.SYS_CHANGE_OPERATION = 'I'
        OR CHANGE_TRACKING_IS_COLUMN_IN_MASK(@ColId, CT.SYS_CHANGE_COLUMNS) = 1
    );
-- Этот запрос вернет новые записи и обновления, где изменился именно столбец StockCount.

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

-- Установка контекста для сеанса (например, для служебных операций)
EXEC sp_set_change_tracking_context @context = 0x01; -- например, ID фоновой задачи

-- Тогда при получении изменений можно их отфильтровать
SELECT *
FROM CHANGETABLE(CHANGES dbo.Products, 100) AS CT
WHERE CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> 0x01;
-- Это позволяет игнорировать изменения, внесенные самой системой синхронизации.