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:

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:

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.

In your page in "Create Function" you said "create function sys.fn_PhysLocCracker (@physical_locator binary (8))"
In SQL you said SELECT sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], * FROM TEST;

[…] To look at this further, the first thing to do is identify the Page ID for a specific row in a database that we can use for tracking the I/O associated with bringing that page into the buffer pool. To do this, we can use the fn_PhysLocFormatter function and %%physloc%% as shown by Paul in his blog post, SQL Server 2008: New (undocumented) physical row locator function. […]