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