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?
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
.