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:

  1. CREATE TABLE Months(MonthId INT, Mon_Name VARCHAR(20))  
  2. GO  
  3. INSERT INTO Months  
  4. SELECT 101, 'January'  
  5. UNION ALL  
  6. SELECT 102, 'February'  
  7. UNION ALL  
  8. SELECT 103, 'March'  
  9. UNION ALL  
  10. SELECT 104, 'April'  
  11. UNION ALL  
  12. SELECT 105, 'May'  
  13. UNION ALL  
  14. SELECT 106, 'June'  
  15. UNION ALL  
  16. SELECT 107, 'July'  
  17. UNION ALL  
  18. SELECT 108, 'August'  
  19. UNION ALL  
  20. SELECT 109, 'September'  
  21. UNION ALL  
  22. SELECT 110, 'October'  
  23. UNION ALL  
  24. SELECT 111, 'November'  
  25. UNION ALL  
  26. SELECT 112, 'December'  
  27. Here, are the below 3 different methods to generate the unique numbers.  
  28.     --Method 1  
  29. SELECT MonthId, Mon_Name, ROW_NUMBER() OVER(ORDER BY Mon_Name) rnum  
  30. FROM Months  
  31. --Method 2  
  32. SELECT MonthId, Mon_Name, COUNT( * ) OVER(ORDER BY MonthId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as rnum  
  33. FROM months  
  34. --Method 3  
  35. SELECT MonthId, Mon_name, (SELECT COUNT( * ) FROM months m1 WHERE m1.MonthId <= m2.MonthId) as rnum  
  36. FROM months m2  
  37. ORDER BY rnum  

 

 

SQL Server

 

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.