SQL: Generate random number within RANGE and not exist in a table

Formula to find the random number is: FLOOR(RAND(CHECKSUM(random seed))*(@upper_range_number-@lower_range_number)+@lower_range_number). NEWID() is most dependable random seed.

select FLOOR(rand(6986)*23567*10000)–retunrs an 9 digit random number

declare @newid uniqueidentifier;
select @newid=NEWID();

select @newid;

select CHECKSUM(@newid);
select RAND(CHECKSUM(@newid));

select FLOOR(RAND(CHECKSUM(@newid))*(9999-1000));
select FLOOR(RAND(CHECKSUM(@newid))*(9999-1000)+1000);

–select FLOOR(RAND(CHECKSUM(NEWID()))*(999999-100000)+100000)

 

 

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