Tags
%%physloc%%, Different Methods to get Row Numbers WITHOUT Order By Clause in SQL Server, How to get row numbers in SQL SERVER, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, MS SQL, New (undocumented) physical row locator function, Queries for SQL Interview, Row Number, Row Numbers WITHOUT Order By Clause in SQL Server, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Joins, SQL Puzzles, SQL Queries, SQL Server, SQL SERVER Interview questions, SQL Skills, SQLSERVER, sys.fn_PhysLocCracker, T SQL Puzzles, TSQL, TSQL Interview questions, TSQL Queries
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-
-- -----------Method 1---------------- SELECT * ,%%physloc%% PhysicalLocation FROM flag f CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) -----------Method 2---------------- SELECT * , %%physloc%% PhysicalLocation FROM flag -- Output of Method 1 given below- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Http://MSBISkills.com
You must be logged in to post a comment.