Development

SQL Server – Various methods to generate unique number sequence

Hi all,

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.

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

Seq_1

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

That’s all for the day folks. Keep learning 🙂

Advertisements

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 )

Google+ photo

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

Connecting to %s