RowID Equivalent in SQLSERVER

Physical location of a row in SQL Server

Introduction

In Oracle, each row can be identified by the ROWID column. It is a pseudo column. This column contains the information about the address of row saved in datafile.

ROWNUM is quite different thing, if we compare it to ROWID, rownum to particular row will not remain fix, it gets change over any dml operation done on a table.

ROWID equivalent in sqlserver, we have  

SQLSERVER 2008 - %%physloc%%
SQLSERVER 2005 - %%lockres%%


QUERY USING PHYSLOC

select %%physloc%% from table_name;

To decode the value of above column, we can use below function
sys.fn_PhysLocFormatter



select %%physloc%% , sys.fn_physlocformatter(%%physloc%%) from table_name;

 

Result will be as below:

0xA901000001000000  (1:425:0)

How to read above format ?
row with ID = ???? is located in the file 1 on page 425 and in slot 0.

using this we can identify the actual data file of the row, using the view sys.database_files.

Some Queries

select * from table_name where %%physloc%% = 0xA901000001000000

this will return row