Add Row Number Based on Physical Location in SQL Server
OR
 Add Row Number WITHOUT Order By Clause in SQL Server

Adding a row number to your SQL code is very common and pretty easy. We can also say that it is almost very common now a days to use a Row Number ( Ranking Functions )

Note – Flag referred in all the queries is a sample table created by me.

Sample Example

--

SELECT 
ROW_NUMBER() OVER ( ORDER BY Tim ASC ) AS RowNumber
, f.*
FROM flag f  

--

Now lets say we want Row Numbers with out this ORDER BY Column Clause. We can use below methods to get row numbers without ORDER by Column.

Different Methods to Get Row Numbers without ORDER BY Column

--

--METHOD 1 | Using SELECT NULL
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RowNumber FROM Flag


--METHOD 2 | Using %%physloc%%
SELECT * , ROW_NUMBER() OVER (ORDER BY %%physloc%%) RowNumber FROM Flag


--METHOD 3 | Using SELECT 1 (You can use any number here)
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber FROM Flag


--Method 4 | Using %%lockres%%
SELECT * , ROW_NUMBER() OVER (ORDER BY %%lockres%%) RowNumber FROM Flag


--Method 5 | Using sys.fn_PhysLocCracker(%%physloc%%) , file_id, page_id, slot_id
--Row Number Based on Physical Location in SQL Server

SELECT f.*,
ROW_NUMBER() OVER(ORDER BY file_id, page_id, slot_id) AS Row
FROM flag f 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
ORDER BY Row

--

What is %%physloc%%

%%physloc%% is a Virtual Column. This column is undocumented. Hence you can use it at you own risk.The %%physloc%% virtual column returns a record locator value similar to the one you can find in the different lock related trace events. Actually each table has a “hidden” column called %%physloc%% which stores a row’s physical location in hexidecimal form. That’s pretty hard to understand. SQL Server offers a way for us to read this information a little more easily: the sys.fn_PhysLocFormatter function, which is present on sqlskills.com. According to Paul Randal of sqlskills.com – It gives the database file, page within the file, and slot number on the page in the format (file:page:slot).

sys.fn_PhysLocCracker – This function cracks the output of %%physloc%% virtual column. If you want to go in detail about this function use below link-

http://www.sqlskills.com/blogs/paul/sql-server-2008-new-undocumented-physical-row-locator-function/#comment-218767

--

-----------Method 1----------------

SELECT * ,%%physloc%% PhysicalLocation
FROM flag f 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)


-----------Method 2----------------
SELECT * , %%physloc%% PhysicalLocation FROM flag

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com