SQL Server has many undocumented functions and commands. I will write series of posts with functions to have a library online.
The first, I’d like to write about %%PhysLoc%% and the function fn_PhyslocCracker
With this function, I can see my table physical location and use DBCC PAGE to see the data in each page.
SELECT * FROM dbo.MyTable AS m CROSS APPLY fn_PhyslocCracker(%%physloc%%) plc; DBCC PAGE(MyDatabse, File_ID, Page_ID, Type) WITH TABLERESULTS