CHANGETABLE (Transact-SQL)

Article
05/23/2023

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Retourne les informations de suivi des modifications d’une table. Vous pouvez utiliser cette instruction pour retourner toutes les modifications d’une table ou des informations de suivi des modifications pour une ligne spécifique.

Conventions de la syntaxe Transact-SQL

Syntaxe

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 ] )

Arguments

MODIFICATIONS table_name , last_sync_version
Retourne les informations de suivi pour toutes les modifications apportées à une table qui s’est produite depuis la version spécifiée par last_sync_version.

table_name
Table définie par l'utilisateur sur laquelle obtenir le suivi des modifications. Le suivi des modifications doit être activé sur la table. Un nom de table en une, deux, trois ou quatre parties peut être utilisé. Le nom de table peut être un synonyme de la table.

last_sync_version
Valeur scalaire bigint nullable. Une expression provoque une erreur de syntaxe. Si la valeur est NULL, toutes les modifications suivies sont retournées. Lorsqu'elle obtient des modifications, l'application appelante doit spécifier le point à partir duquel les modifications sont requises. Le last_sync_version spécifie ce point. La fonction retourne des informations pour toutes les lignes qui ont été modifiées depuis la version considérée. L’application interroge pour recevoir des modifications avec une version supérieure à last_sync_version. En règle générale, avant d’obtenir les modifications, l’application appellera CHANGE_TRACKING_CURRENT_VERSION() pour obtenir la version qui sera utilisée lors des prochaines modifications. Par conséquent, l'application n'a pas besoin d'interpréter ou comprendre la valeur réelle. Étant donné que last_sync_version est obtenu par l’application appelante, l’application doit conserver la valeur. Si l'application perd cette valeur, elle doit alors réinitialiser les données. last_sync_version doit être validé pour s’assurer qu’il n’est pas trop ancien, car certaines ou toutes les informations de modification peuvent avoir été nettoyées en fonction de la période de rétention configurée pour la base de données. Pour plus d’informations, consultez CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL) et les options ALTER DATABASE SET (Transact-SQL).

VERSION table_name, { primary_key_values }
Retourne les informations de suivi des modifications les plus récentes pour une ligne spécifiée. Les valeurs de clé primaire doivent identifier la ligne. primary_key_values identifie les colonnes clés primaires et spécifie les valeurs. Les noms des colonnes clés primaires peuvent être spécifiés dans n'importe quel ordre.

table_name
Table définie par l'utilisateur sur laquelle obtenir les informations de suivi des modifications. Le suivi des modifications doit être activé sur la table. Un nom de table en une, deux, trois ou quatre parties peut être utilisé. Le nom de table peut être un synonyme de la table.

column_name
Spécifie le nom de la colonne ou des colonnes clés primaires. Plusieurs noms de colonne peuvent être spécifiés dans un ordre quelconque.

valeur
Valeur de la clé primaire. S’il existe plusieurs colonnes clés primaires, les valeurs doivent être spécifiées dans le même ordre que les colonnes apparaissent dans la liste column_name .

[ FORCESEEK ]
S’applique à : SQL Server (à partir de SQL Server 2016 (13.x) SP2 CU16, SQL Server 2017 (14.x) CU24 et SQL Server 2019 (15.x) CU11), Azure SQL Database et Azure SQL Managed Instance

Paramètre facultatif qui force l’utilisation d’une opération de recherche pour accéder au table_name. Dans certains cas où très peu de lignes ont changé, une opération d’analyse peut toujours être utilisée pour accéder au table_name. Si une opération d’analyse présente un problème de performances, utilisez le FORCESEEK paramètre.

[AS] table_alias [ (column_alias [ ,...n ] ) ]
Fournit des noms pour les résultats retournés par CHANGETABLE.

table_alias
Nom d'alias de la table retournée par CHANGETABLE. table_alias est obligatoire et doit être un identificateur valide.

column_alias
Alias de colonne facultatif ou liste d'alias de colonne pour les colonnes retournées par CHANGETABLE. Ce paramètre permet de personnaliser les noms de colonne au cas où il existerait des noms en double dans les résultats.

Types de retour

table

Valeurs renvoyées

CHANGETABLE CHANGES

Lorsque CHANGES est spécifié, zéro, une ou plusieurs lignes contenant les colonnes suivantes sont retournées.

Nom de la colonne	Type de données	Description
SYS_CHANGE_VERSION	bigint	Valeur de version associée à la dernière modification apportée à la ligne
SYS_CHANGE_CREATION_VERSION	bigint	Valeurs de version associées à la dernière opération d'insertion.
SYS_CHANGE_OPERATION	nchar(1)	Spécifie le type de modification : U = Mettre à jour I = Insertion D = Supprimer
SYS_CHANGE_COLUMNS	varbinary(4100)	Répertorie les colonnes qui ont été modifiées depuis la version last_sync_version (de référence). Notez que les colonnes calculées ne sont jamais répertoriées comme modifiées. La valeur est NULL si l'une des conditions suivantes est remplie : le suivi des modifications de colonne n'est pas activé ; il s'agit d'une opération d'insertion ou de suppression ; toutes les colonnes dépourvues de clés primaires ont été mises à jour en une seule opération. Cette valeur binaire ne doit pas être interprétée directement. À la place, pour l’interpréter, utilisez CHANGE_TRACKING_IS_COLUMN_IN_MASK().
SYS_CHANGE_CONTEXT	varbinary(128)	Modifiez les informations de contexte que vous pouvez éventuellement spécifier à l’aide de la clause WITH dans le cadre d’une instruction INSERT, UPDATE ou DELETE.
<valeur de colonne clé primaire>	Identique aux colonnes de table utilisateur	Valeurs de clés primaires pour la table faisant l'objet d'un suivi. Ces valeurs identifient de manière unique chaque ligne dans la table utilisateur.

CHANGETABLE VERSION

Lorsque VERSION est spécifié, une ligne contenant les colonnes suivantes est retournée.

Nom de la colonne	Type de données	Description
SYS_CHANGE_VERSION	bigint	Valeur de la version actuelle des modifications associée à la ligne. La valeur est NULL si aucune modification n'a pas été apportée pendant un délai dépassant la période de rétention de suivi des modifications, ou si la ligne n'a pas été modifiée depuis l'activation du suivi des modifications.
SYS_CHANGE_CONTEXT	varbinary(128)	Modifiez les informations de contexte que vous pouvez éventuellement spécifier à l'aide de la clause WITH dans le cadre d'une instruction INSERT, UPDATE ou DELETE.
<valeur de colonne clé primaire>	Identique aux colonnes de table utilisateur	Valeurs de clés primaires pour la table faisant l'objet d'un suivi. Ces valeurs identifient de manière unique chaque ligne dans la table utilisateur.

Notes

La fonction CHANGETABLE est utilisée en général dans la clause FROM d'une requête comme s'il s'agissait d'une table.

CHANGETABLE(CHANGES...)

Pour obtenir des données de ligne pour des lignes nouvelles ou modifiées, joignez le jeu de résultats à la table utilisateur en utilisant les colonnes clés primaires. Une seule ligne est retournée pour chaque ligne de la table utilisateur qui a été modifiée, même si plusieurs modifications ont été apportées à la même ligne depuis la valeur last_sync_version .

Les modifications de colonne clé primaire ne sont jamais marquées comme des mises à jour. La modification d'une valeur de clé primaire est considérée comme une suppression de la valeur ancienne et une insertion de la valeur nouvelle.

Si vous supprimez une ligne puis insérez une ligne dotée de l'ancienne clé primaire, la modification est considérée comme une mise à jour de toutes les colonnes de la ligne.

Les valeurs retournées pour les SYS_CHANGE_OPERATION colonnes et SYS_CHANGE_COLUMNS les colonnes sont relatives à la ligne de base (last_sync_version) spécifiée. Par exemple, si une opération d’insertion a été effectuée à la version 10 et une opération de mise à jour à la version 15, et si la base de référence last_sync_version est 12, une mise à jour est signalée. Si la valeur last_sync_version est 8, une insertion est signalée. SYS_CHANGE_COLUMNS ne signale jamais les colonnes calculées comme ayant été mises à jour.

En général, toutes les opérations d'insertion, de mise à jour ou de suppression de données dans les tables utilisateur font l'objet d'un suivi, y compris l'instruction MERGE.

Les opérations suivantes qui impliquent les données des tables utilisateur ne font pas l'objet d'un suivi :

Exécution de l’instruction UPDATETEXT . Cette instruction est déconseillée et sera supprimée dans une prochaine version de SQL Server. Toutefois, les modifications apportées à l’aide de la .WRITE clause de l’instruction UPDATE sont suivies.
Suppression de lignes à l’aide TRUNCATE TABLEde . Lorsqu'une table est tronquée, les informations sur la version du suivi des modifications associées à la table sont réinitialisées comme si le suivi des modifications venait d'être activé sur la table. Une application cliente doit toujours valider sa dernière version synchronisée. La validation échoue si la table a été tronquée.

CHANGETABLE(VERSION...)

Un jeu de résultats vide est retourné si une clé primaire inexistante est spécifiée.

La valeur de SYS_CHANGE_VERSION la valeur peut être NULL si une modification n’a pas été apportée depuis plus longtemps que la période de rétention (par exemple, le nettoyage a supprimé les informations de modification) ou si la ligne n’a jamais été modifiée depuis que le suivi des modifications a été activé pour la table.

autorisations

Nécessite l’autorisation SELECT sur les colonnes clés primaires et VIEW CHANGE TRACKING l’autorisation sur la table spécifiée par la <valeur table_name> pour obtenir les informations de suivi des modifications.

Exemples

R. Retour de lignes pour une synchronisation initiale des données

L'exemple suivant montre comment obtenir des données pour une synchronisation initiale des données de table. La requête retourne toutes les données de ligne et leurs versions associées. Vous pouvez ensuite insérer ou ajouter ces données au système qui contiendra les données synchronisées.

-- 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. Liste de toutes les modifications apportées depuis une version spécifique

L'exemple suivant répertorie toutes les modifications apportées à une table depuis la version spécifiée (@last_sync_version). [Emp ID] et SSN sont des colonnes dans une clé primaire composite.

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. Obtention de toutes les données modifiées pour une synchronisation

L'exemple suivant montre comment obtenir toutes les données modifiées. Cette requête joint les informations de suivi des modifications à la table utilisateur afin que les informations de la table utilisateur soient retournées. Un LEFT OUTER JOIN est utilisé afin qu'une ligne soit retournée pour les lignes supprimées.

-- 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. Détection des conflits à l'aide de CHANGETABLE(VERSION...)

L'exemple suivant montre comment mettre à jour une ligne uniquement si celle-ci n'a pas changé depuis la dernière synchronisation. Le numéro de version de la ligne spécifique est obtenu à l'aide de CHANGETABLE. Si la ligne a été mise à jour, aucune modification n'est appliquée et la requête retourne des informations sur la modification la plus récente apportée à la ligne.

-- @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);

Voir aussi

Fonctions de suivi des modifications (Transact-SQL)
Suivre les modifications de données (SQL Server)
CHANGE_TRACKING_IS_COLUMN_IN_MASK (Transact-SQL)
CHANGE_TRACKING_CURRENT_VERSION (Transact-SQL)
CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)

Partager via