How to create Row Number Without Ordering Any Columns in sql server.


Row_number function in sql server is used to generate a serial number for a given record set. But we need to use ORDER BY clause so that the numbers are assigned to the specific order. But there is a way. Just do not ORDER BY any columns, but ORDER BY a literal value as shown below.

CREATE TABLE #TEMP (NAMES VARCHAR(100))
INSERT INTO #TEMP
SELECT 'Hitesh' UNION ALL
SELECT 'Abhimanyu' UNION ALL
SELECT 'Priyanka' UNION ALL
SELECT 'Dev' UNION ALL
SELECT 'Dev' UNION ALL
SELECT 'Bhawesh' 

Sql query to generate Row Number Without Ordering Any Columns in sql server

SELECT NAMES, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SNO FROM #Temp
Row_Number

Row_Number