Physical Row Location

Categories: News, Professional, SSC
Comments: No Comments
Published on: April 29, 2011

SQL Server 2008 has presented us a couple of options to aid in becoming better DBA’s.  You can see this evidenced in many ways in the product.  A couple of the things that make me think this is the case boils down to two functions that are new in SQL 2008.  I learned about these while trying to learn how to do something else.  It just so happens that these functions could possibly help me in the other process (I’ll write more about that later when I have finished it).

These new functions are: sys.fn_PhysLocFormatter and sys.fn_PhysLocCracker.  The two functions are really very similar.  The first of the two does as the name implies and formats the physical location, while the second of the two provides a table output of the location.  If you look at the sp_helptext of both, you can see that they only have minor differences.

-------------------------------------------------------------------------------  
-- Name: sys.fn_PhysLocFormatter   
--  
-- Description:  
-- Formats the output of %%physloc%% virtual column  
--  
-- Notes:  
-------------------------------------------------------------------------------  
CREATE FUNCTION sys.fn_PhysLocFormatter (@physical_locator BINARY (8))  
RETURNS VARCHAR (128)  
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)))  
 
 RETURN '(' + CAST (CAST (@FILE_ID AS INT) AS VARCHAR) + ':' +   
     CAST (CAST (@page_id AS INT) AS VARCHAR) + ':' +   
     CAST (CAST (@slot_id AS INT) AS VARCHAR) + ')'  
END

and

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

When you look at these two functions, you can easily say that they are similar right up until the end where they diverge in functionality.  The first casts the data into the “formatted” version, while the cracker simply outputs to a table.

Use of these functions is also quite easy.

usage   
SELECT TOP (10) 
	yt.*,
	pl.FILE_ID, pl.page_id, pl.slot_id
FROM yourtable AS yt
cross apply sys.fn_PhysLocCracker(%%physloc%%) AS pl;
 
SELECT TOP 10
	sys.fn_PhysLocFormatter (%%physloc%%) AS [Physical RID], yt.* 
	FROM yourtable yt;

These functions can prove to be very helpful in your troubleshooting or dives into Internals.  Check them out and enjoy.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
April 2011
M T W T F S S
« Mar   May »
 123
45678910
11121314151617
18192021222324
252627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, April 19, 2014