# How to generate unique random numbers in sql [Answered]RSS

## 5 replies

Last post Apr 01, 2011 02:50 PM by minakshi_23

Member

13 Points

40 Posts

### How to generate unique random numbers in sql

Mar 30, 2011 10:29 AM|minakshi_23|LINK

I have an ID field where I need to insert unique ID's

the ID should be like  -

S- (YY)(MM)9999. where, (YY) is year, (MM) is month and 9999(is a four digit random number)

for example - S-11030001

Any suggestions?

Thanks

Member

101 Points

49 Posts

### Re: How to generate unique random numbers in sql

Mar 30, 2011 11:20 AM|mahioak|LINK

Hi meenakshi,

Try the below query

```DECLARE @Random NVARCHAR(10);--To store 4 digit random number
DECLARE @Final NVARCHAR(MAX)--Final unique random number
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 9999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 9999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

SET @Final = 'S-' + Right(Year(getDate()),2) + RIGHT('0' + RTRIM(MONTH(GETDATE())), 2) + @Random
SELECT @Final```

Thanks

Mahendra

Member

13 Points

40 Posts

### Re: How to generate unique random numbers in sql

Mar 30, 2011 11:27 AM|minakshi_23|LINK

Hi,

I need to also make sure that it's not generating the same number again. so may be need something to check if the number already exists.

Thanks.

Member

101 Points

49 Posts

### Re: How to generate unique random numbers in sql

Mar 30, 2011 11:57 AM|mahioak|LINK

When the random number max limit is upto 9999, we cannot ensure the uniqueness of the random number.

Another simplest way to generate a random number is

`Declare @Random varchar(10)set @Random = CONVERT(varchar(10), right(newid(),10))`

Check the below link for generating a unique random number

## http://beyondrelational.com/blogs/ashish/archive/2009/05/06/generating-unique-random-numeric-and-alpha-numeric-values.aspx

Hope it helps

Thanks

Mahendra

• ### limno

All-Star

119242 Points

8952 Posts

Moderator

### Re: How to generate unique random numbers in sql

Mar 30, 2011 02:41 PM|limno|LINK

You can use an Identity field in your table and calculate your ID based on it without randomness of course.

If you do find a solution (function) to generate your desired random ID, you can use a condition check to make sure it is unique before the INSERT.

Here is the code structure in a SP:

```--======================

--======================
--Your code starts

BEGIN

--Your logic to generate random ID

END

IF NOT EXISTS ( SELECT 1 FROM yourtable  WHERE ID=@ID)

BEGIN

--Your INSERT goes here

END

ELSE

BEGIN

--Your logic to generate another ID

END```
Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Member

13 Points

40 Posts

### Re: How to generate unique random numbers in sql

Apr 01, 2011 02:50 PM|minakshi_23|LINK

Hi,

Finally, got it working.

So, this is the procedure that's generating the random number

ALTER PROCEDURE [dbo].[generateserialnumber]
(
@serialnumber as NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @Random NVARCHAR(10)
DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 1
SET @Upper = 9999
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SET @serialnumber  = 'S-' + RIGHT(Year(getDate()),2) +RIGHT('O' + RTRIM(MONTH(GETDATE())),2) + RIGHT('0000' + LTRIM(STR(@Random)), 4)
END

This is then executed in the insert procedure -

DECLARE @serialnumber nvarchar(MAX)
BEGIN
exec dbo.generateserialnumber  @serialnumber OUTPUT
END
While EXISTS(Select * from nc_details where ncrid = @serialnumber)
BEGIN
exec dbo.generateserialnumber  @serialnumber OUTPUT
END

--insert statement here