Tag Archives: CROSS APPLY

Get Row Number Based on Physical Location in SQL Server

Getting Row Number in SQL Server

Assigning a row number to your results in SQL is a common task and is doing so is pretty straight-forward so I won’t spend a lot of time on it here. Take a look at the syntax here:

USE AdventureWorks2008R2;
GO
SELECT PersonType, FirstName, LastName, 
ROW_NUMBER() OVER(PARTITION BY PersonType ORDER BY FirstName, LastName DESC) AS Row
FROM Person.Person

Row number is determined by ordering your data by certain criteria, using the same syntax as a typical ORDER BY that you’d find at the end of a query. You optionally could specify a way to partition your results (group them into blocks of data). Each partition restarts the row number at 1. If no partition is specified, the entire result set is considered a single partition.

Note that the ORDER BY in your Row Number is mutually exclusive from the ORDER BY of your result set. So it is possible to number your rows in a different order than you return them.

Getting Physical Location of a Row in SQL Server

Mika Wendelius, a Code Project contributor, offers a great explanation of finding a rows physical location on SQL Server using an unsupported feature in SQL Server. Basically, each table has a “hidden” column called %%physloc%% which stores a row’s physical location in hexidecimal form. That’s pretty hard to read but, fortunately, SQL Server offers a way for us to read this information a little more easily: the sys.fn_PhysLocFormatter function, which I found on sqlskills.com (since this is also an unsupported feature on SQL Server). According to sqlskills.com “It gives the database file, page within the file, and slot number on the page in the format (file:page:slot).”

One of the commenters of this on SQL Skills shows a great way to use CROSS APPLY to bring in the results of the PhysLocFormatter function in a more usable fashion. This is the final building block we need in order to number our results based on physical location.

Getting Row Number Based on Physical Location in SQL Server

We know how to number our results based on ordering them a certain way. We also know how to find the physical location of a row in SQL Server.  Now we will put it all together.

USE AdventureWorks2008R2;
GO
SELECT PersonType, FirstName, LastName, 
ROW_NUMBER() OVER(ORDER BY file_id, page_id, slot_id) AS Row
FROM Person.Person 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
ORDER BY Row

With our CROSS APPLY pulling in the physical location data for each row, we can now order our ROW_NUMBER() by file_id, page_id, and slot_id (largest to smallest address info). It really is that simple.

Should I use this in production?

I discovered this clever gem while feeding my curiosity. I have not tested this in a production environment as it was more of an academic excursive for me using unsupported features of SQL Server. I would love to hear if anyone has used these functions in production and how well they performed.