Obtain the physical address information of the SQL server data row (% lockress % & % physloc %)

In SQL Server 2005, you can use% Lockress %Obtain the physical path information corresponding to the data row.% Physloc %Obtain and useSYS. fn_physlocformatterAfter the binary data is converted% Lockress %But these are not described in msdn.

% Lockress %The obtained value consists of three parts: file number, page number, and location number, which are separated by colons.

Select   * , % Lockres % As phys   From Dbtest

The result is:

ID name phys
-- ---------------------------------------------------
1 A 1 : 77 : 0
2 B 1 : 77 : 1
3 C 1 : 77 : 2

Run

Select   * , % Physloc %   As Phys From Dbtest

The result is:

ID name phys
-- -------------------------------------
1 A Zero X 7300000001000000
2 B Zero X 7300000001000100
3 C Zero X 7300000001000200

Run

 Select *, SYS. fn_physlocformatter (%Physloc%) As physFromDbtest

The result is:

ID name phys
-- -----------------------------------------------
1 A ( 1 : 115 : 0 )
2 B ( 1 : 115 : 1 )
3 C ( 1 : 115 : 2 )

Because SQL sever2005 and SQL server2008 are on different machines, the physical information obtained is different. The obtained information is equivalent to a hidden and unique key column in a table, therefore, you can easily solve some problems, such as deleting duplicate data without a primary key table, but there is no relevant description in msdn. Therefore, some risks should be taken into account during use.