Undocumented Physical Row Locator Function in SQL Server 2008

Introduction

Oracle databases use the ROWID data type to store the address of every row in the database table. But SQL Server does not have this this functionality builtin. SQL Server 2008 has introduced virtual columns called %%physloc%%. Using this virtual column, we can identify the physical address of the row in SQL Server.

Example

Suppose I have created one table and inserted some dummy data into it. Now I want to determine the physical address of each row. This can be done by the virtual column %%physloc%%.

CREATE TABLE #TempData
(
Id int,
Name Varchar(20)
)

INSERT INTO #TempData VALUES (1,'Tejas')
INSERT INTO #TempData VALUES (2,'Jignesh')
INSERT INTO #TempData VALUES (3,'Rakesh')
INSERT INTO #TempData VALUES (4,'Umesh')
INSERT INTO #TempData VALUES (5,'Nirav')
INSERT INTO #TempData VALUES (6,'Keyur')

Query to determine physical row locator

SELECT %%lockres%% AS lockres,%%physloc%% AS physloc,* FROM #TempData

Formats the output of %%physloc%% virtual column

The Virtual %%physloc%% gives us the physical row location in a binary format. Using the builtin SQL function fn_physlocFormatter, we can format this binary to string and this string in file_id:page_id:slot_id format. The output of this function is the same as a %%lockres%% virtual column.

SELECT %%lockres%% AS lockres,%%physloc%% AS physloc,
sys.fn_PhysLocFormatter(%%physloc%%) usingfunction,*
FROM #TempData

The output of the function fn_physlocFormatter(%%physloc%%) is the same as the virtual column %%lockres%%. This virtual column is used to determine the locking resource.

Conclusion

Using a %%physloc%% virtual column, we can determine the physical address of a row in binary format and using a builtin function fn_physlocFormatter, we can format it in “file_id:page_id:slot_id”.