sp_helptext 'sys.fn_physLocFormatter'

Text

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

-- Name: sys.fn_PhysLocFormatter

--

-- Description:

--    Formats the output of %%physloc%% virtual column

--

-- Notes:

-------------------------------------------------------------------------------

create function sys.fn_PhysLocFormatter (@physical_locator binary (8))

returns varchar (128)

as

begin

    declare @page_id    binary (4)

    declare @file_id    binary (2)

    declare @slot_id    binary (2)

    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot

    --

    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))

    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))

    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))

    return '(' + cast (cast (@file_id as int) as varchar) + ':' +

                 cast (cast (@page_id as int) as varchar) + ':' +

                 cast (cast (@slot_id as int) as varchar) + ')'

end

SELECT sys.fn_PhysLocFormatter(%%physloc%%) as [Physical RID], *

FROM TBL_A

DBCC TRACEON (3604)

DBCC PAGE(SW_TEST, 1, 55, 1) WITH TABLERESULTS