for peak performance

As part of the Database Corruption Challenge I needed to convert the LSN values returned by fn_dblog() / fn_dump_dblog() to numeric.

The math is not very simple and it is best described by Paul Randal.

• Take the rightmost 4 characters (2-byte log record number) and convert to a 5-character decimal number, including leading zeroes, to get stringA
• Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading zeroes, to get stringB
• Take the leftmost number (4-byte VLF sequence number) and convert to a decimal number, with no leading zeroes, to get stringC
• The LSN string we need is stringC + stringB + stringA

You may download the script that will create the function from here, or use the code below to build your own:

RAISERROR('Create function: [dbo].[ufn_convertLSNToNumeric]', 10, 1) WITH NOWAIT
GO
IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[ufn_convertLSNToNumeric]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufn_convertLSNToNumeric]
GO

CREATE FUNCTION [dbo].[ufn_convertLSNToNumeric]
(		
	@LSN		[varchar](22)
)
RETURNS [numeric](25)
/* WITH ENCRYPTION */
AS

-- ============================================================================
-- Author	 : Dan Andrei STEFAN
-- Create date	 : 26.05.2015
-- Module	 : Database Analysis & Performance Monitoring
-- ============================================================================

/*
	
		
Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN

		
	Take the rightmost 4 characters (2-byte log record number) and convert to a 5-character decimal number, including leading zeroes, to get stringA
	Take the middle number (4-byte log block number) and convert to a 10-character decimal number, including leading zeroes, to get stringB
	Take the leftmost number (4-byte VLF sequence number) and convert to a decimal number, with no leading zeroes, to get stringC
	The LSN string we need is stringC + stringB + stringA

	00000001:00000001:0001
*/
begin
	RETURN	CAST(CAST(CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + LEFT(@LSN, 8), 8), 1) As int)  AS VARCHAR(32)) as varchar(8)) + 
			CAST(RIGHT(REPLICATE('0', 10) + CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + SUBSTRING(@LSN, 10, 8), 8), 1) As int)  AS VARCHAR(32)), 10) as varchar(10)) + 
			CAST(RIGHT(REPLICATE('0', 5) + CAST(CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + RIGHT(@LSN, 4), 8), 1) As int)  AS VARCHAR(32)), 5) as varchar(5)) AS [numeric](25))
end

GO