Obtention de modifications à l'aide de fonctions de suivi des modifications

À propos des fonctions de suivi des modifications

Les applications peuvent utiliser les fonctions suivantes pour obtenir les modifications apportées à une base de données et les informations concernant ces modifications.

• Fonction CHANGETABLE(CHANGES...)
  Cette fonction d'ensemble de lignes permet d'exécuter des requêtes d'informations de modifications. Elle interroge les données stockées dans les tables de suivi des modifications internes. Elle renvoie un jeu de résultats qui contient les clés primaires des lignes qui ont changé, ainsi que d'autres informations de modification telles que l'opération, les colonnes mises à jour et la version de la ligne.

  La fonction CHANGETABLE(CHANGES...) prend une dernière version synchronisée en tant qu'argument. La sémantique de la dernière version de synchronisation est la suivante :

  • Le client appelant a obtenu les modifications et connaît toutes les modifications jusqu'à la dernière version de synchronisation comprise.

  • La fonction CHANGETABLE(CHANGES ...) retourne par conséquent toutes les modifications qui se sont produites après la dernière version de synchronisation.

    L'illustration suivante montre comment CHANGETABLE(CHANGES…) est utilisée pour obtenir des modifications.

    

• Fonction CHANGE_TRACKING_CURRENT_VERSION()
  Permet d'obtenir la version actuelle qui sera utilisée la prochaine fois lorsque l'interrogation change. Cette version représente la version de la dernière transaction validée.

• Fonction CHANGE_TRACKING_MIN_VALID_VERSION()
  Permet d'obtenir la version valide minimale qu'un client doit avoir pour obtenir des résultats valides à partir de CHANGETABLE(). Le client doit vérifier la dernière version de synchronisation par rapport à la valeur renvoyée par cette fonction. Si la dernière version de synchronisation est inférieure à la version retournée par cette fonction, le client ne pourra pas obtenir de résultats valides de CHANGETABLE() et devra effectuer une réinitialisation.

Obtention des données initiales

Pour pouvoir obtenir des modifications pour la première fois, une application doit envoyer une requête afin d'obtenir les données initiales et la version de synchronisation. L'application doit obtenir les données appropriées directement à partir de la table, puis utiliser CHANGE_TRACKING_CURRENT_VERSION() pour obtenir la version initiale. Cette version sera passée à CHANGETABLE (CHANGES…) la première fois que des modifications seront obtenues.

L'exemple suivant montre comment obtenir la version de synchronisation initiale et le jeu de données initial.

    -- Obtain the current synchronization version. This will be used next time that changes are obtained.
    SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

    -- Obtain initial data set.
    SELECT
        P.ProductID, P.Name, P.ListPrice
    FROM
        SalesLT.Product AS P

Utilisation de fonctions de suivi des modifications pour obtenir des modifications

Pour obtenir les lignes modifiées d'une table ainsi que des informations sur les modifications, utilisez CHANGETABLE(CHANGES…). Par exemple, la requête suivante obtient les modifications pour la table SalesLT.Product.

SELECT
    CT.ProductID, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT

Habituellement, un client souhaite obtenir les données les plus récentes d'une ligne plutôt qu'uniquement les clés primaires de la ligne. Par conséquent, une application doit joindre les résultats de CHANGETABLE(CHANGES…) aux données de la table utilisateur. Par exemple, la requête suivante établit une jointure avec la table SalesLT.Product afin d'obtenir les valeurs des colonnes Name et ListPrice. Notez l'utilisation de OUTER JOIN. Cela est nécessaire afin de s'assurer que les informations relatives aux modifications sont retournées pour les lignes qui ont été supprimées de la table utilisateur.

SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

Pour obtenir la version à utiliser dans l'énumération de modification suivante, utilisez CHANGE_TRACKING_CURRENT_VERSION(), comme indiqué dans l'exemple suivant.

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()

Lorsqu'une application obtient des modifications, elle doit utiliser à la fois CHANGETABLE(CHANGES…) et CHANGE_TRACKING_CURRENT_VERSION(), comme indiqué dans l'exemple suivant.

-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

Numéros de versions

Une base de données pour laquelle le suivi des modifications est activé possède un compteur de version qui s'incrémente à mesure que des modifications sont apportées afin de modifier des tables faisant l'objet d'un suivi. Chaque ligne modifiée comporte un numéro de version associé. Lorsqu'une demande est envoyée à une application afin de connaître les modifications, une fonction fournissant un numéro de version est appelée. La fonction retourne des informations à propos de toutes les modifications apportées depuis cette version. À certains égards, le concept de version de suivi des modifications est semblable au type de données rowversion.

Validation de la dernière version synchronisée

Les informations relatives aux modifications sont maintenues pour une durée limitée. Cette durée est contrôlée par le paramètre CHANGE_RETENTION qui peut être spécifié dans le cadre d'ALTER DATABASE.

Notez que la durée spécifiée pour CHANGE_RETENTION détermine la fréquence à laquelle toutes les applications doivent demander des modifications à la base de données. Si une application a une valeur de last_synchronization_version antérieure à la version de synchronisation minimale valide pour une table, cette application ne peut pas effectuer d'énumération de modification valide. Cela est dû au fait que certaines informations de modification ont pu être nettoyées. Avant d'obtenir des modifications à l'aide de CHANGETABLE(CHANGES…), l'application doit valider la valeur de last_synchronization_version qu'elle prévoie de passer à CHANGETABLE(CHANGES…). Si la valeur de last_synchronization_version n'est pas valide, cette application doit réinitialiser toutes les données.

L'exemple suivant montre vérifier la validité de la valeur de last_synchronization_version pour chaque table.

    -- Check individual table.
    IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(
                                       OBJECT_ID('SalesLT.Product')))
    BEGIN
      -- Handle invalid version and do not enumerate changes.
      -- Client must be reinitialized.
    END

Comme l'exemple suivant le montre, la validation de la valeur de last_synchronization_version peut être vérifiée par rapport à toutes les tables de la base de données.

    -- Check all tables with change tracking enabled
    IF EXISTS (
      SELECT COUNT(*) FROM sys.change_tracking_tables
      WHERE min_valid_version > @last_synchronization_version )
    BEGIN
      -- Handle invalid version & do not enumerate changes
      -- Client must be reinitialized
    END

Utilisation du suivi de colonne

Le suivi de colonne permet aux applications d'obtenir les données relatives uniquement aux colonnes qui ont changé, plutôt que la ligne entière. Par exemple, considérez le scénario dans lequel une table possède une ou plusieurs grandes colonnes qui sont rarement modifiées et d'autres colonnes qui changent fréquemment. Sans le suivi de colonne, une application peut déterminer uniquement qu'une ligne a changé et elle doit synchroniser toutes les données, y compris celles des grandes colonnes. Grâce au suivi de colonne, une application peut déterminer si les données des grandes colonnes ont changé et synchroniser les données uniquement si elles ont changé.

Les informations de suivi de colonne apparaissent dans la colonne SYS_CHANGE_COLUMNS retournée par la fonction CHANGETABLE(CHANGES ...).

Le suivi de colonne peut être utilisé de sorte que NULL soit retourné pour une colonne qui n'a pas changé. Si la colonne peut prendre la valeur NULL, une colonne séparée doit être retournée afin d'indiquer si la colonne a changé.

Dans l'exemple suivant, la colonne CT_ThumbnailPhoto sera NULL si elle n'a pas changé. Elle pourrait également être NULL si elle a pris la valeur NULL ; l'application peut utiliser CT_ThumbNailPhoto_Changed afin de déterminer si la colonne a changé.

DECLARE @PhotoColumnId int = COLUMNPROPERTY(
    OBJECT_ID('SalesLT.Product'),'ThumbNailPhoto', 'ColumnId')

SELECT
    CT.ProductID, P.Name, P.ListPrice, -- Always obtain values.
    CASE
           WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) = 1
            THEN ThumbNailPhoto
            ELSE NULL
      END AS CT_ThumbNailPhoto,
      CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) AS
                                   CT_ThumbNailPhoto_Changed
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
     CT.SYS_CHANGE_CONTEXT
FROM
     SalesLT.Product AS P
INNER JOIN
     CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
     P.ProductID = CT.ProductID AND
     CT.SYS_CHANGE_OPERATION = 'U'

Obtention de résultats cohérents et corrects

L'obtention des données modifiées pour une table requiert plusieurs étapes. Sachez que des résultats incohérents ou incorrects peuvent être retournés si certains problèmes ne sont pas pris en compte et résolus.

Par exemple, pour obtenir les modifications apportées à une table Sales et une table SalesOrders, une application doit effectuer les étapes suivantes :

1. Valider la dernière version synchronisée à l'aide de CHANGE_TRACKING_MIN_VALID_VERSION().

2. Obtenir la version qui peut être utilisée pour obtenir les modifications lors de la prochaine interrogation à l'aide de CHANGE_TRACKING_CURRENT_VERSION().

3. Obtenir les modifications de la table Sales à l'aide de CHANGETABLE(CHANGES…).

4. Obtenir les modifications de la table SalesOrders à l'aide de CHANGETABLE(CHANGES…).

Deux processus qui se produisent dans la base de données peuvent affecter les résultats retournés par les étapes précédentes :

• Le processus de nettoyage s'exécute en arrière-plan et supprime les informations de suivi des modifications antérieures à la période de rétention spécifiée.

  Le processus de nettoyage est un processus d'arrière-plan distinct qui utilise la période de rétention spécifiée lorsque vous configurez le suivi des modifications pour la base de données. Le problème réside dans le fait que le processus de nettoyage peut se produire entre la validation de la dernière version de synchronisation et l'appel à CHANGETABLE(CHANGES…). Une dernière version de synchronisation qui était juste valide peut ne plus l'être au moment où les modifications sont obtenues. Par conséquent, des résultats incorrects peuvent être retournés.

• Des opérations DML sont en cours dans les tables Sales et SalesOrders, telles que les suivantes :

  • Des modifications peuvent être apportées aux tables après que la version pour la prochaine fois a été obtenue à l'aide de CHANGE_TRACKING_CURRENT_VERSION(). Par conséquent, le nombre de modifications retournées peut être plus important que prévu.

  • Une transaction pourrait être validée entre l'appel visant à obtenir les modifications de la table Sales et l'appel visant à obtenir les modifications de la table SalesOrders. Par conséquent, les résultats de la table SalesOrder pourraient avoir une valeur de clé étrangère qui n'existe pas dans la table Sales.

Pour éviter de rencontrer les problèmes mentionnés ci-dessus, nous vous recommandons d'utiliser le niveau d'isolement de capture instantanée. Cela contribue à garantir la cohérence des informations de modification et à éviter les conditions de concurrence liées à la tâche de nettoyage en arrière-plan. Si vous n'utilisez pas de transactions de capture instantanée, le développement d'une application qui utilise le suivi des modifications peut nécessiter davantage d'efforts.

Utilisation du niveau d'isolement de capture instantanée

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT
    SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  -- Verify that version of the previous synchronization is valid.
  -- Obtain the version to use next time.
  -- Obtain changes.
COMMIT TRAN

Alternatives à l'utilisation du niveau d'isolement de capture instantanée

Voir aussi

Tâches

Concepts