SQL Server - Various Methods To Generate Number Sequence

Friends, most of the time we used ranking function like ROW_NUMBER() when it required to generate unique number sequence. But there also other ways to accomplish the same thing without using ranking function. I will tell you 3 various ways to generate the unique number sequence including ranking function.

Most of the time we use ranking function like ROW_NUMBER() when required to generate a unique number sequence. But there are also other ways to accomplish the same thing without using ranking function. I will tell you 3 various ways to generate the unique number sequence including ranking function.

Let’s create a demo table with some dummy data:

CREATE TABLE Months(MonthId INT, Mon_Name VARCHAR(20))
GO
INSERT INTO Months
SELECT 101, 'January'
UNION ALL
SELECT 102, 'February'
UNION ALL
SELECT 103, 'March'
UNION ALL
SELECT 104, 'April'
UNION ALL
SELECT 105, 'May'
UNION ALL
SELECT 106, 'June'
UNION ALL
SELECT 107, 'July'
UNION ALL
SELECT 108, 'August'
UNION ALL
SELECT 109, 'September'
UNION ALL
SELECT 110, 'October'
UNION ALL
SELECT 111, 'November'
UNION ALL
SELECT 112, 'December'
Here, are the below 3 different methods to generate the unique numbers.
    --Method 1
SELECT MonthId, Mon_Name, ROW_NUMBER() OVER(ORDER BY Mon_Name) rnum
FROM Months
--Method 2
SELECT MonthId, Mon_Name, COUNT( * ) OVER(ORDER BY MonthId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rnum
FROM months
--Method 3
SELECT MonthId, Mon_name, (SELECT COUNT( * ) FROM months m1 WHERE m1.MonthId <= m2.MonthId) as rnum
FROM months m2
ORDER BY rnum

 

 

 

After executing these queries we will get the sequence of unique numbers. We can choose any method according to our choice to generate the numbers sequence.

That’s all for today folks. Keep learning.