How to do row numbering without using any column in the ORDER BY COLUMN

Problem:

Row numbering without specifying any column in the order by or partition.

Solution:

ROW_NUMBER, starting SQL Server 2008 or higher.

Order by using a literal value for example SELECT 0

Example:

Let’s do a row numbering for an employee table without using any order by columns in the ROW_NUMBER Function. For that we might need to use a small trick by specifying a literal value in the order by clause. We can also see the execution plan to see whether the sorting is still in the operator cost.

Please see the sample script below:

SELECT

       [Name]

      ,[PhoneNumber]

      ,[EmployeeType]

      ,[Company_Id]

      ,ROW_NUMBER() OVER ( ORDER BY ( SELECT 0) ) as row_num

FROM [dbo].[Employees]

Please view the results of the comparison with out and with order by in the below screenshot

  

Below is the screenshot that shows the execution plans for both the queries.