sql-server - SQL Server 更改数据捕获 - 操作类型 3 在非 NULL 的字段中显示 NULLS

Question

我们刚刚开始在 SQL Server 2017 上使用变更数据捕获。我正在通过执行一些简单的 UPDATE 语句然后检查 CDC 表来运行一些测试。

要查看表更改，我们运行 CDC 函数 cdc.fn_cdc_get_all_changes 传递 row_filter_option 参数 all update old 这样我们就可以看到在任何更新语句之前和之后。

该函数的结果令人困惑，在为最终用户创建报告之前，我需要了解它们为何显示:

在结果中，每个表 UPDATE 由 2 行表示 - 操作类型为 3 的行表示 UPDATE 语句之前的行，操作类型为 3 的行4 表示 UPDATE 语句之后的行。混淆与 BEFORE 行有关:我看到该行中的许多字段显示为 NULL，而该字段在 UPDATE 之前绝对不是 NULL。我找不到关于哪些字段在 BEFORE 行中显示为 NULL 的任何可定义模式。 AFTER 行似乎没有问题，并且显示的记录与 UPDATE 之后的样子完全一样。

有人可以帮助我理解为什么在运行 UPDATE 之前，某些字段中有值时 BEFORE 记录显示为 NULL 吗？

示例:

如果我运行以下查询:

SELECT *
FROM dbo.Profiles
WHERE Id = 1

结果是这样的:

Id    Name      DateOfBirth   Position    Gender   Country
1     John Doe  1971-10-12    President   Male     France

然后运行:

UPDATE dbo.Profiles
SET DateOfBirth = '1971-10-11'
WHERE Id = 1

最后，运行CDC函数查看变化:

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Profiles  
  (@from_lsn, @to_lsn, N'all update old'); 

这是两个结果的样子:

_$start_lsn _$operation Id   Name      DateOfBirth   Position   Gender  Country
001         3           1    John Doe  1971-10-12    NULL       NULL    France
002         4           1    John Doe  1971-10-11    President  Male    France

我试图理解为什么第一行的 Position 和 Gender 为 NULL？

编辑 1:

根据要求，这是函数的代码。此功能的 MS 文档可在此处找到:https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql

function [cdc].[fn_cdc_get_all_changes_dbo_Profiles]
(   @from_lsn binary(10),
    @to_lsn binary(10),
    @row_filter_option nvarchar(30)
)
returns table
return

select NULL as __$start_lsn,
    NULL as __$seqval,
    NULL as __$operation,
    NULL as __$update_mask, NULL as [Id], NULL as [Country], NULL as [DateOfBirth], NULL as [Gender], NULL as [Name], NULL as [NativeName], NULL as [PlaceOfBirth], NULL as [Position], NULL as [SynchedDateTime], NULL as [BirthName], NULL as [Name_Normalized], NULL as [Name_Parts], NULL as [BirthName_Normalized], NULL as [BirthName_Parts], NULL as [NativeName_Normalized], NULL as [NativeName_Parts], NULL as [Alias], NULL as [Children], NULL as [Fathers], NULL as [Godparents], NULL as [Mothers], NULL as [Relatives], NULL as [Siblings], NULL as [Spouses], NULL as [Stepparents], NULL as [Education], NULL as [Employer], NULL as [MemberOf], NULL as [UnmarriedPartner], NULL as [AwardReceived], NULL as [Convicted], NULL as [NotableWork], NULL as [PoliticalParty], NULL as [Addresses], NULL as [Affiliations], NULL as [DateOfDeath], NULL as [DeletedDateTime], NULL as [Status], NULL as [SyncBatch], NULL as [DeleteReason], NULL as [CountryCode]
where ( [sys].[fn_cdc_check_parameters]( N'dbo_Profiles', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 0)

union all

select t.__$start_lsn as __$start_lsn,
    t.__$seqval as __$seqval,
    t.__$operation as __$operation,
    t.__$update_mask as __$update_mask, t.[Id], t.[Country], t.[DateOfBirth], t.[Gender], t.[Name], t.[NativeName], t.[PlaceOfBirth], t.[Position], t.[SynchedDateTime], t.[BirthName], t.[Name_Normalized], t.[Name_Parts], t.[BirthName_Normalized], t.[BirthName_Parts], t.[NativeName_Normalized], t.[NativeName_Parts], t.[Alias], t.[Children], t.[Fathers], t.[Godparents], t.[Mothers], t.[Relatives], t.[Siblings], t.[Spouses], t.[Stepparents], t.[Education], t.[Employer], t.[MemberOf], t.[UnmarriedPartner], t.[AwardReceived], t.[Convicted], t.[NotableWork], t.[PoliticalParty], t.[Addresses], t.[Affiliations], t.[DateOfDeath], t.[DeletedDateTime], t.[Status], t.[SyncBatch], t.[DeleteReason], t.[CountryCode]
from [cdc].[dbo_Profiles_CT] t with (nolock)    
where (lower(rtrim(ltrim(@row_filter_option))) = 'all')
    and ( [sys].[fn_cdc_check_parameters]( N'dbo_Profiles', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1)
    and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4)
    and (t.__$start_lsn <= @to_lsn)
    and (t.__$start_lsn >= @from_lsn)

union all   

select t.__$start_lsn as __$start_lsn,
    t.__$seqval as __$seqval,
    t.__$operation as __$operation,
    t.__$update_mask as __$update_mask, t.[Id], t.[Country], t.[DateOfBirth], t.[Gender], t.[Name], t.[NativeName], t.[PlaceOfBirth], t.[Position], t.[SynchedDateTime], t.[BirthName], t.[Name_Normalized], t.[Name_Parts], t.[BirthName_Normalized], t.[BirthName_Parts], t.[NativeName_Normalized], t.[NativeName_Parts], t.[Alias], t.[Children], t.[Fathers], t.[Godparents], t.[Mothers], t.[Relatives], t.[Siblings], t.[Spouses], t.[Stepparents], t.[Education], t.[Employer], t.[MemberOf], t.[UnmarriedPartner], t.[AwardReceived], t.[Convicted], t.[NotableWork], t.[PoliticalParty], t.[Addresses], t.[Affiliations], t.[DateOfDeath], t.[DeletedDateTime], t.[Status], t.[SyncBatch], t.[DeleteReason], t.[CountryCode]
from [cdc].[dbo_Profiles_CT] t with (nolock)     
where (lower(rtrim(ltrim(@row_filter_option))) = 'all update old')
    and ( [sys].[fn_cdc_check_parameters]( N'dbo_Profiles', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1)
    and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4 or
         t.__$operation = 3 )
    and (t.__$start_lsn <= @to_lsn)
    and (t.__$start_lsn >= @from_lsn)

Answer 1

类型 3 CDC 行检索特定数据类型的空值，例如 nvarchar(max)，除非该特定列已被 UPDATE 语句更改。引用:https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql?view=sql-server-ver15