sql-serverreplicationdebezium

What's the difference between sys.fn_cdc_get_max_lsn() and max value of cdc.lsn_time_mapping


In Debezium's sqlserver, we can see this:

String GET_MAX_LSN = "SELECT [#db].sys.fn_cdc_get_max_lsn()";
String GET_MAX_TRANSACTION_LSN = "SELECT MAX(start_lsn) FROM [#db].cdc.lsn_time_mapping WHERE tran_id <> 0x00";

When I look up what sys.fn_cdc_get_max_lsn means, it show this(See https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-cdc-get-max-lsn-transact-sql?view=sql-server-ver16):

This function returns the maximum LSN in the start_lsn column of the cdc.lsn_time_mapping table. As such, it is the last LSN processed by the capture process when changes are propagated to the database change tables. It serves as the high endpoint for the all timelines that are associated with capture instances defined for the database.

It seems same with GET_MAX_TRANSACTION_LSN? I wonder what the difference between sys.fn_cdc_get_max_lsn() and SELECT MAX(start_lsn) FROM [#db].cdc.lsn_time_mapping?

I wonder what the difference between sys.fn_cdc_get_max_lsn() and SELECT MAX(start_lsn) FROM [#db].cdc.lsn_time_mapping?


Solution

  • There is no difference. If you look at the definition of sys.fn_cdc_get_max_lsn(), you can see it (basically) does the same as your query:

    create function [sys].[fn_cdc_get_max_lsn]()                
    returns binary(10)
    as                                                  
    begin
                                                            
        declare @max_lsn binary(10)
        
        select @max_lsn = 0x00000000000000000000
            
        select @max_lsn = max(start_lsn)                            
        from [cdc].[lsn_time_mapping]
                                        
        return @max_lsn
                                                    
    end                                                 
    

    So the only difference between your SELECT and the built in function is the WHERE you have. Both get the MAX value of start_lsn from cdc.lsn_time_mapping.