Sunday, October 13, 2013

RowID Equivalent in SQLSERVER

Physical location of a row in SQL Server


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


select %%physloc%% from table_name;

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

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

No comments:

Post a Comment

web stats