cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)

  • Article

S’applique à :SQL Server

Retourne une ligne de modification nette pour chaque ligne source modifiée dans la plage LSN (Log Sequence Numbers) spécifiée.

Attendez, qu’est-ce qu’un LSN ? Chaque enregistrement du journal des transactions SQL Server est identifié de manière unique par un numéro de séquence de journal (LSN). Les LSN sont classés de telle sorte que si LSN2 est supérieur à LSN1, la modification décrite par l’enregistrement de journal auquel fait référence LSN2 s’est produite après la modification décrite par le LSN de l’enregistrement de journal.

Le LSN d’un enregistrement de journal où un événement important s’est produit peut être utile pour construire des séquences de restauration correctes. Étant donné que les LSN sont classés, vous pouvez les comparer pour l’égalité et l’inégalité (c’est-à-dire, <, , >= , <=, >=). Ces comparaisons sont utiles pour créer des séquences de restauration.

Lorsqu’une ligne source a plusieurs modifications au cours de la plage LSN, une seule ligne qui reflète le contenu final de la ligne est retournée par la fonction d’énumération décrite ci-dessous. Par exemple, si une transaction insère une ligne dans la table source et qu’une transaction suivante dans la plage LSN met à jour une ou plusieurs colonnes de cette ligne, la fonction ne retourne qu’une seule ligne, qui inclut les valeurs de colonne mises à jour.

Cette fonction d'énumération est créée lorsqu'une table source est activée pour la capture des données modifiées et que le suivi net est spécifié. Pour activer le suivi net, la table source doit avoir une clé primaire ou un index unique. Le nom de la fonction est dérivé et utilise le format cdc.fn_cdc_get_net_changes_<capture_instance>, où <capture_instance> est la valeur spécifiée pour la capture instance lorsque la table source a été activée pour la capture de données modifiées. Pour plus d’informations, consultez sys.sp_cdc_enable_db (Transact-SQL).

Conventions de la syntaxe Transact-SQL

Syntaxe

  
cdc.fn_cdc_get_net_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' )  
  
<row_filter_option> ::=  
{ all  
 | all with mask  
 | all with merge  
}

Arguments

from_lsn

Numéro séquentiel dans le journal qui représente le point de terminaison inférieur de la plage de numéros séquentiels dans le journal à inclure dans le jeu de résultats. from_lsn est binary(10).

Uniquement les lignes dans le cdc.[ capture_instance]_CT table de modifications avec une valeur en __$start_lsn supérieure ou égale à from_lsn sont incluses dans le jeu de résultats.

to_lsn

Numéro séquentiel dans le journal qui représente le point de terminaison supérieur de la plage de numéros séquentiels dans le journal à inclure dans le jeu de résultats. to_lsn est binary(10).

Uniquement les lignes dans le cdc.[ capture_instance]_CT table de modification dont la valeur dans __$start_lsn est inférieure ou égale à from_lsn ou égale à to_lsn sont incluses dans le jeu de résultats.

<>row_filter_option ::= { all | all with mask | all with merge }

Option qui régit le contenu des colonnes de métadonnées aussi bien que les lignes retournées dans le jeu de résultats. Il peut s'agir de l'une des options suivantes :

all
Retourne le numéro séquentiel dans le journal de la modification finale apportée à la ligne et de l'opération permettant d'appliquer la ligne aux colonnes de métadonnées __$start_lsn et __$operation. La colonne __$update_mask a toujours la valeur NULL.

all with mask
Retourne le numéro séquentiel dans le journal de la modification finale apportée à la ligne et de l'opération permettant d'appliquer la ligne aux colonnes de métadonnées __$start_lsn et __$operation. De plus, lorsqu'une opération de mise à jour retourne (__$operation = 4) les colonnes capturées modifiées dans la mise à jour sont marquées dans la valeur retournée dans __$update_mask.

all with merge
Retourne le numéro séquentiel dans le journal de la modification finale apportée à la ligne dans les colonnes de métadonnées __$start_lsn. La colonne __$operation affichera l'une des deux valeurs suivantes : 1 pour la suppression et 5 pour indiquer que l'opération permettant d'appliquer la modification est une insertion ou une mise à jour. La colonne __$update_mask a toujours la valeur NULL.

Dans la mesure où la logique permettant de déterminer l'opération précise pour une modification donnée augmente la complexité de la requête, cette option est conçue pour améliorer le performances des requêtes lorsqu'il suffit d'indiquer que l'opération nécessaire pour appliquer la donnée modifiée est une insertion ou une mise à jour, mais il n'est pas utile de différencier explicitement les deux. Cette option est plus attrayante dans les environnements cibles où une opération de fusion est disponible directement.

Table retournée

Nom de la colonne Type de données Description
__$start_lsn binary(10) Numéro séquentiel dans le journal associé à la transaction de validation de la modification.

Toutes les modifications validées dans la même transaction partagent le même numéro séquentiel dans le journal de validation. Par exemple, si une opération de mise à jour sur la table source modifie deux colonnes en deux lignes, la table de modifications contient quatre lignes, chacune avec le même __$start_lsnvalue.
__$operation int Identifie l'opération du langage de manipulation de données permettant d'appliquer la ligne de données de modification à la source de données cible.

Si la valeur du paramètre row_filter_option est tout ou tout avec le masque, la valeur dans cette colonne peut être l'une des valeurs suivantes :

1 = suppression

2 = insertion

4 = mise à jour

Si la valeur du paramètre row_filter_option est tout ou tout avec fusion, la valeur dans cette colonne peut être l'une des suivantes :

1 = suppression

5 = insérer ou mettre à jour
__$update_mask varbinary(128) Masque de bits avec un bit correspondant à chaque colonne capturée identifiée pour l'instance de capture. Tous les bits définis de cette valeur ont la valeur 1 lorsque __$operation = 1 ou 2. Lorsque __$operation = 3 ou 4, seuls les bits qui correspondent aux colonnes qui ont changé ont la valeur 1.
<colonnes des tables sources capturées> varie Les colonnes restantes retournées par la fonction sont les colonnes de la table source qui ont été identifiées comme colonnes capturées lorsque l'instance de capture a été créée. Si aucune colonne n'a été spécifiée dans la liste des colonnes capturées, toutes les colonnes de la table source sont retournées.

Autorisations

Requiert l'appartenance au rôle serveur fixe sysadmin ou au rôle de base de données fixe db_owner. Pour tous les autres utilisateurs, requiert l'autorisation SELECT sur toutes les colonnes capturées dans la table source et, si un rôle de régulation pour l'instance de capture a été défini, l'appartenance à ce rôle de base de données. Lorsque l’appelant n’a pas l’autorisation d’afficher les données sources, la fonction retourne une ligne avec des valeurs NULL pour toutes les colonnes.

Notes

Les modifications apportées à l’identificateur unique d’une ligne entraînent fn_cdc_get_net_changes l’affichage de la commande UPDATE initiale avec une commande DELETE, puis INSERT à la place. Ce comportement est nécessaire pour suivre la clé avant et après la modification.

L’erreur 313 est attendue si la plage LSN fournie n’est pas appropriée lors de l’appel cdc.fn_cdc_get_all_changes_<capture_instance> ou cdc.fn_cdc_get_net_changes_<capture_instance>. Si le lsn_value paramètre est au-delà de la durée du LSN le plus bas ou du LSN le plus élevé, l’exécution de ces fonctions retourne dans l’erreur 313 : Msg 313, Level 16, State 3, Line 1 An insufficient number of arguments were supplied for the procedure or function. Cette erreur doit être gérée par le développeur. L’exemple T-SQL pour une solution de contournement est disponible sur ReplTalk sur GitHub.

Exemples

L’exemple suivant utilise la fonction cdc.fn_cdc_get_net_changes_HR_Department pour signaler les modifications nettes apportées à la table HumanResources.Department source pendant un intervalle de temps spécifique.

En premier lieu, la fonction GETDATE est utilisée pour marquer le début de l'intervalle de temps. Après avoir appliqué à la table source plusieurs instructions DML, la fonction GETDATE est rappelée pour identifier la fin de l'intervalle de temps. La fonction sys.fn_cdc_map_time_to_lsn est ensuite utilisée pour mapper l’intervalle de temps à une plage de requête de capture de données modifiées limitée par des valeurs LSN. Enfin, la fonction cdc.fn_cdc_get_net_changes_HR_Department est interrogée pour obtenir les modifications nettes apportées à la table source pendant l'intervalle de temps. Remarquez que la ligne qui est insérée, puis supprimée n'apparaît pas dans le jeu de résultats retourné par la fonction. En effet, une ligne qui est d'abord ajoutée, puis supprimée dans une fenêtre de requête ne produit aucune modification nette dans la table source pendant l'intervalle.

Notes

Avant d’exécuter cet exemple, vous devez d’abord exécuter l’exemple B dans sys.sp_cdc_enable_table (Transact-SQL) pour activer la capture de données modifiées sur la table HumanResources.Department. Dans l’exemple ci-dessous, HR_Department est le nom de l’instance de capture CDC, comme spécifié dans sys.sp_cdc_enable_table.

USE AdventureWorks2022;  
GO  
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
-- Obtain the beginning of the time interval.  
SET @begin_time = DATEADD(day, -1, GETDATE()) ;  
-- DML statements to produce changes in the HumanResources.Department table.  
INSERT INTO HumanResources.Department (Name, GroupName)  
VALUES (N'MyDept', N'MyNewGroup');  
  
UPDATE HumanResources.Department  
SET GroupName = N'Resource Control'  
WHERE GroupName = N'Inventory Management';  
  
DELETE FROM HumanResources.Department  
WHERE Name = N'MyDept';  
  
-- Obtain the end of the time interval.  
SET @end_time = GETDATE();  
-- Map the time interval to a change data capture query range.  
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
SET @from_lsn = ISNULL(sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time), [sys].[fn_cdc_get_min_lsn]('HR_Department') );
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);  
  
-- Return the net changes occurring within the query window.  
SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@from_lsn, @to_lsn, 'all');

Voir aussi