Поделиться через

CHANGETABLE (Transact-SQL)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

CHANGETABLE (  
    { CHANGES <table_name> , <last_sync_version> 
    | VERSION <table_name> , <primary_key_values> } 
    , [ FORCESEEK ] 
    )  
[AS] <table_alias> [ ( <column_alias> [ ,...n ] )  
  
<primary_key_values> ::=  
( <column_name> [ , ...n ] ) , ( <value> [ , ...n ] )

Аргументы

ИЗМЕНЕНИЯ table_name , last_sync_version
Возвращает сведения об отслеживании всех изменений в таблице, которая произошла с тех пор, как версия, указанная last_sync_version.

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

last_sync_version
Значение скалярного скалярного набора, допускающее значение null . Выражение вызывает синтаксическую ошибку. При значении NULL возвращаются все отслеживаемые изменения. При получении изменений вызывающее приложение должно указать точку, с которой необходимы эти изменения. В last_sync_version указывается эта точка. Функция возвращает данные обо всех строках, изменившихся начиная с этой версии. Приложение запрашивает получение изменений с версией больше last_sync_version. Как правило, перед получением изменений приложение вызовет CHANGE_TRACKING_CURRENT_VERSION() получение версии, которая будет использоваться при следующем изменении. Поэтому в приложении нет необходимости интерпретировать или разбирать фактическое значение. Так как last_sync_version получается вызывающим приложением, приложение должен сохранить значение. Если это значение в приложении будет утеряно, потребуется повторная инициализация данных. last_sync_version следует проверить, чтобы убедиться, что она не слишком старая, так как некоторые или все сведения об изменениях могли быть удалены в соответствии с периодом хранения, настроенным для базы данных. Дополнительные сведения см. в статьях CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) и ALTER DATABASE SET Options (Transact-SQL).

ВЕРСИЯ table_name, { primary_key_values }
Возвращает информацию о последнем изменении указанной строки. Значения первичного ключа должны идентифицировать строку. primary_key_values определяет столбцы первичного ключа и задает значения. Имена первичных ключевых столбцов могут быть указаны в любом порядке.

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

column_name
Указывает одно или несколько имен первичных ключевых столбцов. Несколько имен столбцов могут быть указаны в любом порядке.

значение
Значение первичного ключа. Если есть несколько столбцов первичного ключа, значения должны быть указаны в том же порядке, что и столбцы в списке column_name .

[ FORCESEEK ]
Область применения: SQL Server (начиная с SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) CU16, SQL Server 2017 (14.x) CU24 и SQL Server 2019 (15.x) CU11), База данных SQL Azure и Управляемый экземпляр SQL Azure

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

[AS] table_alias [ (column_alias [ ,...n ] ] ]
Задает имена для результатов, возвращаемых функцией CHANGETABLE.

table_alias
Псевдоним таблицы, возвращаемый функцией CHANGETABLE. table_alias является обязательным и должен быть допустимым идентификатором.

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

Типы возвращаемых данных

table

Возвращаемые значения

CHANGETABLE CHANGES

При указании ключевого слова CHANGES возвращается ноль или несколько строк, содержащих следующие столбцы.

Имя столбца Тип данных Description
SYS_CHANGE_VERSION bigint Значение версии, связанное с последним изменением в строке
SYS_CHANGE_CREATION_VERSION bigint Значения версии, связанные с последней операцией вставки.
SYS_CHANGE_OPERATION nchar(1) Задает тип изменения:

U = update

I = Insert

D = Delete
SYS_CHANGE_COLUMNS varbinary(4100) Содержит список столбцов, измененных после last_sync_version (базовой версии). Обратите внимание, что вычисляемые столбцы никогда не перечислены как измененные.

Принимает значение NULL, если выполняется любое из следующих условий.

Отслеживание изменений столбцов не включено.

Операция представляет собой операцию вставки или удаления.

Все ключевые столбцы, не являющиеся первичными, были обновлены одной операцией. Это двоичное значение не следует интерпретировать непосредственно. Вместо этого, чтобы интерпретировать его, используйте CHANGE_TRACKING_IS_COLUMN_IN_MASK().
SYS_CHANGE_CONTEXT varbinary(128) Измените сведения о контексте, которые можно дополнительно указать с помощью предложения WITH в рамках инструкции INSERT, UPDATE или DELETE.
<Значение столбца первичного ключа> Такие же, как столбцы таблицы пользователя Значения первичного ключа для отслеживаемой таблицы. Эти значения уникально идентифицируют каждую строку в таблице пользователя.

CHANGETABLE VERSION

При указании значения VERSION возвращается одна строка, содержащая следующие столбцы.

Имя столбца Тип данных Description
SYS_CHANGE_VERSION bigint Текущее значение версии изменений, связанное со строкой.

Принимает значение NULL, если изменение не производилось в течение периода времени, превышающего срок хранения данных отслеживания изменений, либо если строка не изменялась с момента включения отслеживания изменений.
SYS_CHANGE_CONTEXT varbinary(128) Измените контекст, который указывается дополнительно с использованием предложения WITH как часть инструкции INSERT, UPDATE или DELETE.
<Значение столбца первичного ключа> Такие же, как столбцы таблицы пользователя Значения первичного ключа для отслеживаемой таблицы. Эти значения уникально идентифицируют каждую строку в таблице пользователя.

Замечания

Функция CHANGETABLE обычно используется в предложении FROM запроса, как если бы она была таблицей.

CHANGETABLE(CHANGES...)

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

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

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

Значения, возвращаемые для SYS_CHANGE_OPERATION столбцов, SYS_CHANGE_COLUMNS относятся к базовым (last_sync_version), заданным. Например, если операция вставки была выполнена в версии 10 и операция обновления в версии 15, и если базовый last_sync_version является 12, будет сообщено обновление. Если значение last_sync_version имеет значение8, будет сообщаться вставка. SYS_CHANGE_COLUMNS никогда не будет сообщать вычисляемые столбцы как обновленные.

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

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

  • Выполнение инструкции UPDATETEXT . Эта инструкция устарела и будет удалена в будущей версии SQL Server. Однако изменения, внесенные с помощью .WRITE предложения инструкции UPDATE, отслеживаются.

  • Удаление строк с помощью TRUNCATE TABLE. При усечении таблицы данные отслеживания изменений, связанные с таблицей, будут сброшены, как будто отслеживание изменений для таблицы только что включено. Клиентское приложение должно обязательно произвести синхронизацию версий. Проверка окончится неуспешно, если таблица была усечена.

CHANGETABLE(VERSION...)

Если указан несуществующий первичный ключ, возвращается пустой результирующий набор.

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

Разрешения

SELECT Требуется разрешение на столбцы первичного ключа и VIEW CHANGE TRACKING разрешение на таблицу, указанную <значением table_name> для получения сведений об отслеживании изменений.

Примеры

А. Возврат строк для начальной синхронизации данных

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

-- Get all current rows with associated version  
SELECT e.[Emp ID], e.SSN, e.FirstName, e.LastName,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_CONTEXT  
FROM Employees AS e  
CROSS APPLY CHANGETABLE   
    (VERSION Employees, ([Emp ID], SSN), (e.[Emp ID], e.SSN)) AS c;

B. Список всех изменений, внесенных после определенной версии

В следующем примере показано, как получить список всех изменений, внесенных в таблицу после указанной версии (@last_sync_version). [Emp ID] и SSN являются столбцами составного первичного ключа.

DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT [Emp ID], SSN,  
    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,  
    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS C;

C. Получение всех измененных данных для синхронизации

В следующем примере показано, как можно получить все измененные данные. Этим запросом данные отслеживания изменений объединяются с пользовательской таблицей таким образом, чтобы был выполнен возврат данных пользовательской таблицы. Ключевое слово LEFT OUTER JOIN используется для возврата строки для удаленных строк.

-- Get all changes (inserts, updates, deletes)  
DECLARE @last_sync_version bigint;  
SET @last_sync_version = <value obtained from query>;  
SELECT e.FirstName, e.LastName, c.[Emp ID], c.SSN,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION,  
    c.SYS_CHANGE_COLUMNS, c.SYS_CHANGE_CONTEXT   
FROM CHANGETABLE (CHANGES Employees, @last_sync_version) AS c  
    LEFT OUTER JOIN Employees AS e  
        ON e.[Emp ID] = c.[Emp ID] AND e.SSN = c.SSN;

D. Выявление конфликтов с помощью инструкции CHANGETABLE(VERSION...)

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

-- @last_sync_version must be set to a valid value  
UPDATE  
    SalesLT.Product  
SET  
    ListPrice = @new_listprice  
FROM  
    SalesLT.Product AS P  
WHERE  
    ProductID = @product_id AND  
    @last_sync_version >= ISNULL (  
        (SELECT CT.SYS_CHANGE_VERSION FROM   
            CHANGETABLE(VERSION SalesLT.Product,  
            (ProductID), (P.ProductID)) AS CT),  
        0);

См. также

функции Отслеживание изменений (Transact-SQL)
Отслеживание изменений данных (SQL Server)
CHANGE_TRACKING_IS_COLUMN_IN_MASK (Transact-SQL)
CHANGE_TRACKING_CURRENT_VERSION (Transact-SQL)
CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)