I have noticed in the SQL Server documentation that Log Sequence Numbers (LSNs) are sometimes described as numeric(25,0) and other times as binary(10). Why are there two different data types used for LSNs? When should I use each type?
LSNs are values of data type numeric(25,0). Arithmetic operations (for example, addition or subtraction) are not meaningful and must not be used with LSNs.
start_lsn binary(10)
Much as 1st January, 2000 and 2000-01-01 are two different representations of the same date so there are different representation of an LSN depending on how it will be consumed.
I have a test database called Sandbox. I can look at it's internal metadata held on page 9 of file 1 (reference) using the DBCC PAGE.
dbcc traceon(3604);
dbcc page('Sandbox', 1, 9, 3);
This shows that the database was backed up at a certain LSN.
dbi_dbbackupLSN = 43:24059:73 (0x0000002b:00005dfb:0049)
The LSN is shown in both hex and equivalent decimal representation. For example, 0x2b(hex) = 43(decimal). So we can infer that, internally, LSNs are stored in a three-part structure as integer-like values. If we count the bytes ignoring punctuation we see it needs 10 bytes. This is likely the format surfaced in the implementation of CDC.
We can take each of these parts and translate their maximum hex values to the integer equivalent.
0xffffffff = 4294967295 (10 digits)
0xffff = 65535 (5 digits)
If we combine these we see we need a total length of 10+10+5 = 25 digits to represent the same value in decimal. This is the format more commonly used in documented functionality. To confirm we can look at the same backup LSN using msdb.
select database_backup_lsn
from msdb..backupset
where database_name = 'Sandbox'
database_backup_lsn
-------------------
43000002405900073
You'll notice that this number is the same as the the one pulled from the boot page with the parts zero-padded and concatenated.
So you should use the datatype corresponding to the source from which you are reading.
There's a handy-dandy script here to do this translation.
