SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.
Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:
USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;
As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:
SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.
If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:
EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- 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
The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.
HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
