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
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
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
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
0 comments:
Post a Comment