Generate Row Serial Numbers in SQL Query

Sometime we might don't want to apply ordering on our result set to add serial number. But if we are going to use ROW_NUMBER() then we have to have a ORDER BY clause. So, for that we can simply apply a tricks to avoid any ordering on the result set.

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS ItemNo, ItemName FROM ItemMastetr

For that we don't need to apply order by on our result set. We'll just add ItemNo on our given result set.

select 
  ROW_NUMBER() Over (Order by CustomerID) As [S.N.], 
  CustomerID , 
  CustomerName, 
  Address, 
  City, 
  State, 
  ZipCode  
from Customers;

Implementing Serial Numbers Without Ordering Any of the Columns

Demo SQL Script-

IF OBJECT_ID('Tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable;

CREATE TABLE #TestTable (Names VARCHAR(75), Random_No INT); 

INSERT INTO #TestTable (Names,Random_No) VALUES
 ('Animal', 363)
,('Bat', 847)
,('Cat', 655)
,('Duet', 356)
,('Eagle', 136)
,('Frog', 784)
,('Ginger', 690); 

SELECT * FROM #TestTable;

There are ‘N’ methods for implementing Serial Numbers in SQL Server. Hereby, We have mentioned the Simple Row_Number Function to generate Serial Numbers.

ROW_NUMBER() Function is one of the Window Functions that numbers all rows sequentially (for example 1, 2, 3, …) It is a temporary value that will be calculated when the query is run. It must have an OVER Clause with ORDER BY. So, we cannot able to omit Order By Clause Simply. But we can use like below-

SQL Script

IF OBJECT_ID('Tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable;

CREATE TABLE #TestTable (Names VARCHAR(75), Random_No INT); 

INSERT INTO #TestTable (Names,Random_No) VALUES
 ('Animal', 363)
,('Bat', 847)
,('Cat', 655)
,('Duet', 356)
,('Eagle', 136)
,('Frog', 784)
,('Ginger', 690); 

SELECT Names,Random_No,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SERIAL_NO FROM #TestTable;

In the Above Query, We can Also Use SELECT 1, SELECT ‘ABC’, SELECT ” Instead of SELECT NULL. The result would be Same.

SELECT ROW_NUMBER()  OVER (ORDER BY  ColumnName1) As SrNo, ColumnName1,  ColumnName2 FROM  TableName

select ROW_NUMBER() over (order by pk_field ) as srno 
from TableName

Using Common Table Expression (CTE)

WITH CTE AS(
    SELECT ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNumber,
    Customers.*
    FROM Customers
)
SELECT * FROM CTE

I found one solution for MYSQL its easy to add new column for SrNo or kind of tepropery auto increment column by following this query:

SELECT @ab:=@ab+1 as SrNo, tablename.* FROM tablename, (SELECT @ab:= 0)
AS ab

ALTER function dbo.FN_ReturnNumberRows(@Start int, @End int) returns @Numbers table (Number int) as
begin
    insert into @Numbers
    select n = ROW_NUMBER() OVER (ORDER BY n)+@Start-1 from (
    select top (@End-@Start+1) 1 as n from information_schema.columns as A
        cross join information_schema.columns as B
        cross join information_schema.columns as C
        cross join information_schema.columns as D
        cross join information_schema.columns as E) X
    return
end
GO

select * from dbo.FN_ReturnNumberRows(10,9999)