CHANGETABLE: примеры (SQL)

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

Пример 1: Получение всех изменений с момента определенной версии.

-- Предположим, что последняя известная версия клиента - 5
DECLARE @last_version bigint = 5;
SELECT 
    SYS_CHANGE_VERSION,
    SYS_CHANGE_OPERATION,
    EmployeeID, -- Столбец первичного ключа
    FirstName
FROM CHANGETABLE(CHANGES dbo.Employees, @last_version) AS CT
LEFT JOIN dbo.Employees AS E ON CT.EmployeeID = E.EmployeeID
WHERE SYS_CHANGE_VERSION > @last_version;

SYS_CHANGE_VERSION | SYS_CHANGE_OPERATION | EmployeeID | FirstName
-------------------|----------------------|------------|-----------
6                  | U                    | 1          | Иван
7                  | I                    | 5          | Мария

Пример 2: Получение текущей версии изменения для конкретной строки.

SELECT SYS_CHANGE_VERSION
FROM CHANGETABLE(VERSION dbo.Employees, (1)) AS CT; -- EmployeeID = 1

SYS_CHANGE_VERSION
-------------------
6

Пример 3: Использование NULL для получения всех отслеживаемых изменений.

SELECT SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, EmployeeID
FROM CHANGETABLE(CHANGES dbo.Employees, NULL) AS CT;

SYS_CHANGE_VERSION | SYS_CHANGE_OPERATION | EmployeeID
-------------------|----------------------|------------
2                  | I                    | 1
3                  | I                    | 2
6                  | U                    | 1

Похожие функции и механизмы в MS SQL

CHANGE_TRACKING_MIN_VALID_VERSION() - функция, возвращающая минимальную версию, для которой еще доступны сведения об изменениях в таблице. Используется для проверки актуальности кэшированных данных на клиенте перед вызовом CHANGETABLE.

CHANGE_TRACKING_CURRENT_VERSION() - возвращает версию, связанную с последней зафиксированной транзакцией в базе данных. Эта версия используется как отправная точка для следующего запроса CHANGETABLE.

WITH CHANGE_TRACKING_CONTEXT - предложение, позволяющее передать контекстные данные (например, идентификатор приложения) при выполнении DML-операций. Эти данные потом можно получить в столбце SYS_CHANGE_CONTEXT результата CHANGETABLE.

Полное отслеживание изменений (Change Data Capture - CDC) - более тяжеловесная альтернатива. CDC фиксирует не только факт, но и полные данные об изменениях (старые и новые значения). CHANGETABLE предпочтительнее для сценариев синхронизации, где важна скорость и минимальный объем данных, а CDC - для аудита или миграции данных, где требуется детальная информация.

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

PostgreSQL: Механизм Logical Decoding и расширение pg_recvlogical позволяют получать поток изменений на уровне транзакций. Более простой аналог - триггеры, записывающие изменения в отдельную таблицу журнала.

-- Пример триггера в PostgreSQL для журналирования
CREATE TABLE change_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation CHAR(1),
    old_data JSONB,
    new_data JSONB,
    change_time TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_employees_change() 
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_log(table_name, operation, old_data, new_data)
    VALUES ('employees', TG_OP, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Oracle: Oracle GoldenGate и XStream API предоставляют комплексные решения для захвата изменений. Для встроенного отслеживания часто используются триггеры или представления FLASHBACK для запроса исторических данных.

MySQL: Прямого встроенного аналога нет. Применяются бинарные логи (binlog) для репликации, которые можно парсить с помощью сторонних библиотек, или триггеры для кастомного журналирования.

SQLite: Отсутствуют встроенные механизмы отслеживания изменений. Единственный вариант - создание собственной системы с использованием триггеров для записи изменений в отдельную таблицу.

Ключевое отличие MS SQL CHANGETABLE - это встроенная, простая в настройке и легковесная система, не требующая написания дополнительного кода триггеров или парсинга сложных логов.

Типичные ошибки при работе с функцией

Ошибка 1: Запрос изменений для таблицы, в которой не включено отслеживание изменений.

-- Для таблицы dbo.Products не включен CHANGE TRACKING
SELECT * FROM CHANGETABLE(CHANGES dbo.Products, 0) AS CT;

Msg 22109, Level 16, State 1, Line 1
Change tracking is not enabled on table 'dbo.Products'.

Ошибка 2: Неверное количество или порядок значений первичного ключа в аргументе VERSION.

-- Первичный ключ таблицы состоит из двух столбцов (OrderID, ProductID)
-- Передан только один аргумент
SELECT SYS_CHANGE_VERSION
FROM CHANGETABLE(VERSION dbo.OrderDetails, (1001)) AS CT;

Msg 22125, Level 16, State 1, Line 1
The number of primary key column values provided (1) does not match the number of primary key columns in the change tracked table (2).

Ошибка 3: Использование устаревшей версии, которая уже очищена (ниже минимально допустимой).

DECLARE @old_version bigint = 1;
-- Предположим, CHANGE_TRACKING_MIN_VALID_VERSION вернула 5
SELECT * FROM CHANGETABLE(CHANGES dbo.Employees, @old_version) AS CT;

-- Запрос может вернуть пустой набор или ошибку, в зависимости от контекста.
-- Всегда следует проверять минимальную версию перед запросом.

Ошибка 4: Попытка интерпретации SYS_CHANGE_COLUMNS без вспомогательной функции CHANGE_TRACKING_IS_COLUMN_IN_MASK.

-- Неправильно
SELECT SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES dbo.Employees, 0);
-- Правильно: использовать функцию для проверки столбца
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(
    COLUMNPROPERTY(OBJECT_ID('dbo.Employees'), 'Salary', 'ColumnId'),
    SYS_CHANGE_COLUMNS
) AS IsSalaryChanged
FROM CHANGETABLE(CHANGES dbo.Employees, 0) AS CT;

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

Функция CHANGETABLE появилась в SQL Server 2008 вместе с самой технологией Change Tracking. С тех пор ее ядро и синтаксис остаются стабильными, без кардинальных изменений в последующих версиях (2008 R2, 2012, 2014, 2016, 2017, 2019, 2022).

Основные улучшения связаны с общей экосистемой отслеживания изменений и производительностью ядра СУБД. В более новых версиях рекомендуется использовать Change Tracking в сочетании с функциями обработки JSON (в SQL Server 2016+) для сериализации результатов или с In-Memory OLTP (с определенными ограничениями) для повышения производительности.

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

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

DECLARE @last_sync_version bigint = 10;
SELECT 
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_OPERATION,
    E.*,
    -- Проверка, изменилась ли конкретная колонка (например, Salary)
    CHANGE_TRACKING_IS_COLUMN_IN_MASK(
        COLUMNPROPERTY(OBJECT_ID('dbo.Employees'), 'Salary', 'ColumnId'),
        CT.SYS_CHANGE_COLUMNS
    ) AS IsSalaryChanged
FROM CHANGETABLE(CHANGES dbo.Employees, @last_sync_version) AS CT
LEFT JOIN dbo.Employees AS E ON CT.EmployeeID = E.EmployeeID
WHERE CT.SYS_CHANGE_OPERATION IN ('I', 'U') -- Только вставки и обновления
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(
        COLUMNPROPERTY(OBJECT_ID('dbo.Employees'), 'DepartmentID', 'ColumnId'),
        CT.SYS_CHANGE_COLUMNS
    ) = 1; -- И если изменился DepartmentID

Пример 2: Пакетная обработка изменений с использованием контекста.

-- 1. Клиентское приложение записывает изменения с контекстом
DECLARE @context varbinary(128) = CAST('MobileApp_v1.2' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
UPDATE dbo.Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 3;

-- 2. Сервер синхронизации идентифицирует источник изменений
SELECT 
    SYS_CHANGE_VERSION,
    SYS_CHANGE_OPERATION,
    EmployeeID,
    CAST(SYS_CHANGE_CONTEXT AS NVARCHAR(128)) AS ChangeSource
FROM CHANGETABLE(CHANGES dbo.Employees, 50) AS CT
WHERE SYS_CHANGE_CONTEXT IS NOT NULL;

SYS_CHANGE_VERSION | SYS_CHANGE_OPERATION | EmployeeID | ChangeSource
-------------------|----------------------|------------|--------------
51                 | U                    | 25         | MobileApp_v1.2

Пример 3: Использование в процедуре инкрементальной загрузки данных (ETL).

CREATE PROCEDURE usp_LoadEmployeeChanges
    @LastLoadVersion bigint OUTPUT
AS
BEGIN
    DECLARE @CurrentVersion bigint;
    SET @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

    -- Если первая загрузка
    IF (@LastLoadVersion IS NULL)
    BEGIN
        INSERT INTO Staging.Employees (EmployeeID, Name, Dept, LoadVersion)
        SELECT EmployeeID, FirstName, DepartmentID, @CurrentVersion
        FROM dbo.Employees;
    END
    ELSE
    BEGIN
        -- Инкрементальная загрузка изменений
        MERGE Staging.Employees AS Tgt
        USING (
            SELECT 
                CT.EmployeeID,
                E.FirstName,
                E.DepartmentID,
                CT.SYS_CHANGE_OPERATION
            FROM CHANGETABLE(CHANGES dbo.Employees, @LastLoadVersion) AS CT
            LEFT JOIN dbo.Employees AS E ON CT.EmployeeID = E.EmployeeID
        ) AS Src ON Tgt.EmployeeID = Src.EmployeeID
        WHEN MATCHED AND Src.SYS_CHANGE_OPERATION IN ('U', 'D') THEN
            UPDATE SET 
                Tgt.Name = Src.FirstName,
                Tgt.Dept = Src.DepartmentID,
                Tgt.IsDeleted = CASE WHEN Src.SYS_CHANGE_OPERATION = 'D' THEN 1 ELSE 0 END,
                Tgt.LoadVersion = @CurrentVersion
        WHEN NOT MATCHED BY TARGET AND Src.SYS_CHANGE_OPERATION = 'I' THEN
            INSERT (EmployeeID, Name, Dept, LoadVersion)
            VALUES (Src.EmployeeID, Src.FirstName, Src.DepartmentID, @CurrentVersion);
    END

    SET @LastLoadVersion = @CurrentVersion;
END;

Пример 4: Объединение изменений из нескольких таблиц в единый поток.

DECLARE @version bigint = 100;
SELECT 'Employees' AS TableName, SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, EmployeeID AS PK_ID
FROM CHANGETABLE(CHANGES dbo.Employees, @version)
UNION ALL
SELECT 'Projects' AS TableName, SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION, ProjectID AS PK_ID
FROM CHANGETABLE(CHANGES dbo.Projects, @version)
ORDER BY SYS_CHANGE_VERSION;