Acquiring the physical location of a row
SQL Server 2008 introduced a new virtual system column: “%%physloc%%”. “%%physloc%%” returns the file_id, page_id and slot_id information for the current row, in a binary format. Thankfully, SQL Server also includes a couple of functions to split this binary data into a more useful format. Unfortunately, Microsoft has not documented either the column or the functions.
Read on for two functions you can use to format this data more nicely, as well as a short re-write Wayne did to improve performance of one of them.