SQL SERVER – Generating Row Number Without Ordering Any Columns

Row_number function is used to generate a serial number for a given record set. But you need to always use ORDER BY clause so that the numbers are assigned to the specific order.

Let us create the following dataset

CREATE TABLE #TEST (NAMES VARCHAR(100))
INSERT INTO #TEST
SELECT 'PINAL' UNION ALL
SELECT 'MAHESH' UNION ALL
SELECT 'SUNIL' UNION ALL
SELECT 'ARVIND' UNION ALL
SELECT 'MURUGAN'

Suppose you want to generate row number, you can use the following statement

SELECT *,ROW_NUMBER() OVER (ORDER BY NAMES) AS SNO FROM #TEST

The reasult is

The numbers are assigned based on ascending order of name

But what if you want to generate row numbers in the same order the data are added.

Can you omit the ORDER BY Clause?

SELECT *,ROW_NUMBER() OVER () AS SNO FROM #TEST

The above throws the following error

Msg 4112, Level 15, State 1, Line 1
The function ‘ROW_NUMBER’ must have an OVER clause with ORDER BY.

But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below

SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS SNO FROM #TEST

The result is

In place of SELECT 100, you can use anything like SELECT 1, SELECT ‘A’, SELECT NULL, etc

Reference: Pinal Dave (https://blog.sqlauthority.com)