Friday, December 6, 2013

How to get Page ID for row(s) in a table in SQL Server

There can be situations like in my previous post
Attempt to fetch logical page # in database failed
when you need to know to which page belong row(s)
in a table.

Here a query that can help you:

SELECT TOP 100 plc.*, c.*
FROM  dbo.Customers as c (nolock)
      CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc

It gives you the database file id, page id within the file,
and slot number on the page:

There is also "sys.fn_physlocFormatter" function that simply
formats the output of the "fn_physLocCracker" function.

SELECT TOP 100 plc.*,
                   sys.fn_physlocFormatter(%%physloc%%) as [file_id:page_id:slot_id],g.GameID
FROM  dbo.Games as g (nolock)
             CROSS APPLY sys.fn_physLocCracker (%%physloc%%) As plc
WHERE g.gameid=1

Keep in mind that the function "fn_physLocCracker"
is undocumented and exists only in SQL Server 2008 and above.

No comments:

Post a Comment