Find actual Row Location by fn_PhysLocFormatter in your database in SQL Server 2008

There are many undocumented and unsupported functions available in Microsoft SQL Server. You won’t find it even in BOL since it is undocumented but many of them are really very useful. I was digging about filegroups, pages and memory allocation in Microsoft SQL Server 2008 and suddenly found one function “sys.fn_PhysLocFormatter”. This function is used to use for finding the real location of the row in SQL Server. It can give you File id, page no and slot no.  Let us see one small example.

create table RowLocater
(
      id bigint,
      FirstName char(2000),
      LastName char(2000),
      Country char(2000)
)
insert into RowLocater
SELECT 1,‘Ritesh’,‘Shah’,‘India’ Union ALL
SELECT 2,‘Dharmesh’,‘Kalaria’,‘USA’
CREATE TABLE RowLocater1
(
      id bigint,
      FirstName char(200),
      LastName char(200),
      Country char(200),
)
insert into RowLocater1
SELECT 1,‘Ritesh’,‘Shah’,‘India’ Union ALL
SELECT 2,‘Dharmesh’,‘Kalaria’,‘USA’
GO
select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location],
FirstName 
from RowLocater
GO
–result of select is here in my server (BIG TABLE)
–Physical_Location  ID   FirstName                                            
————————————-
–(1:2776:0)          1   Ritesh
–(1:2780:0)          2   Dharmesh
select sys.fn_PhysLocFormatter(%%physloc%%)AS[Physical_Location], 
FirstName  
from RowLocater1
GO
–result of above query in my server (SMALL TABLE)
–Physical_Location  id       FirstName
———————————————
–(1:2777:0)          1       Ritesh                                               –(1:2777:1)          2       Dharmesh                                                                                                                                                                                                 
One more interesting thing to observe, you might know that one page contain 8KB of data in SQL Server, our first table is big one so no pages can contain more than one row, however small table has small datatype and you can see both records falls under only one page (2777). This page # may be different in your own server but the concept will remain same.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: