One of the drawbacks of not being in the SQL team at Microsoft any longer is that I don’t know about all the undocumented features in the next release – I have to hunt around for them like everyone else :-(


So I was poking about in SSMS in 2008 CTP-6 and noticed a function called sys.fn_PhysLocCracker that I’d never heard of. Doing an sp_helptext on it gets the following output:



– Name: sys.fn_PhysLocCracker
–
– Description:
– Cracks the output of %%physloc%% virtual column
–
– Notes:
——————————————————————————-
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
 [file_id] int not null,
 [page_id] int not null,
 [slot_id] int not null
)
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)))
 
 insert into @dumploc_table values (@file_id, @page_id, @slot_id)
 return
end


Cool – but something else I’ve never heard of %%physloc%% – what’s that? After playing around for a while, I figured out how to make it work.  Just to be confusing, there’s another identical version of the function called sys.fn_PhysLocFormatter – and that’s the only one I could get to work. Here’s an example:



CREATE TABLE TEST (c1 INT IDENTITY, c2 CHAR (4000) DEFAULT ‘a’);
GO
INSERT INTO TEST DEFAULT VALUES;
INSERT INTO TEST DEFAULT VALUES;
INSERT INTO TEST DEFAULT VALUES;
GO


SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * FROM TEST;
GO


Physical RID       c1
—————–  ———–
(1:411:0)          1
(1:411:1)          2
(1:413:0)          3


It’s a physical-record locator function! Undocumented and unsupported (obviously), but hey, some of the best features are :-) It gives the database file, page within the file, and slot number on the page in the format (file:page:slot). I can think of a *bunch* of uses for this which I’ll be exploring over the next few months.


How cool is that?!?!

Related Posts