Basic Logics · Real Scenario Logic's

Implementing Serial Numbers Without Ordering Any of the Columns

Introduction

In this Article, We are going to see How to Implement Serial Numbers Without Ordering Any of the Columns in SQL Server.

Demo Table – 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;

Demo Table – Table with Data

Implementing Serial Number Without Ordering Any of the columns

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;

Image Pic

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

Conclusion

In this Article, we saw how to implement Serial Numbers Without Ordering Any of the columns in SQL Server by using ROW_NUMBER() Function. These Queries are unit tested with both On-Premises and Azure Sql db.

Thanks for reading!

Advertisement

One thought on “Implementing Serial Numbers Without Ordering Any of the Columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s