Generate Random values from SQL

Question

It appear that SQL Server like most other products Random Function really is not that random. So we have this nice little function to generate a 10 char value. Is there a better way to accomplish what the following does. I am betting there is.

DECLARE @SaltCount INT;
SELECT @SaltCount = COUNT(*) FROM tmp_NewLogin;
PRINT 'Set Salt values for all records' + CAST(@SaltCount AS VARCHAR(10))
DECLARE @CharPool CHAR(83);
DECLARE @Salt VARCHAR(10);
SET @CharPool = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!"#$%&()*+,-./:;<=>?@';

SET NOCOUNT ON;
updateSaltValue:
    SET @Salt = ''
    SELECT @Salt = @Salt + SUBSTRING(@CharPool, number, 1) FROM
    (
    SELECT TOP 10 number FROM MASTER..[spt_values] WHERE TYPE = 'p' AND Number BETWEEN 1 AND 83
    ORDER BY NEWID()
    ) AS t

    UPDATE TOP(1) [table] SET [Salt] = @Salt WHERE [Salt] IS NULL

IF (@@ROWCOUNT > 0)
    GOTO updateSaltValue

SET NOCOUNT OFF;

PRINT 'Completed setting salts for all records';

In what way are you finding the RAND() function to not be random? You need to properly seed it, but it could just be that your seeding method has a problem. — Tom H
– Tom H, Commented Nov 6, 2008 at 16:41

user34850 · Accepted Answer · 2008-11-06 18:09:52Z

9

Most programmers make a mistake of reinventing the randomization functionality and end up with something that is not random at all. I'd recommend you to stick with built-in RAND() function. Seed it once then fetch as many values as you need.

answered Nov 6, 2008 at 18:09

user34850

1,9921 gold badge11 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BoltBait Over a year ago

The word "once" in your answer should be large, red, and blinking.

user12861 · Accepted Answer · 2008-11-06 20:15:16Z

3

Reinventing RAND is a recipe for disaster. Where have you ever noticed it behaving incorrectly? I don't think you even need to seed it. SQL Server should seed it on its own just fine. Seeding should just be necessary when you need to produce the same "random" sequence several times when testing algorithms or some such.

answered Nov 6, 2008 at 20:15

user12861

2,4464 gold badges25 silver badges43 bronze badges

Comments

Brian Dukes · Accepted Answer · 2008-11-06 16:53:50Z

1

According to books-on-line for rand() function: If seed is not specified, the Microsoft SQL Server 2005 Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.
You can avoid this with quick & dirty trick:

Create view like this:

create view [dbo].[wrapped_rand_view]
as
select rand( ) as random_value

Next create function that reads from the view:

create function [dbo].[wrapped_rand]()
returns float
as
begin
declare @f float
set @f = (select random_value from wrapped_rand_view)
return @f

In this way you have random seed each time when you call your wrapped_rand() function and distinct random value between 0 and 1.

edited Nov 6, 2008 at 16:53

Brian Dukes

157k25 gold badges152 silver badges176 bronze badges

answered Nov 6, 2008 at 15:56

Aleksandar

1,3491 gold badge12 silver badges17 bronze badges

3 Comments

user12861 Over a year ago

This doesn't make sense to me. How is it different from just using rand() directly?

Aleksandar Over a year ago

It's assigning new seed value each time you call wrapped_rand() function so you don't have to manually set random seed which you must do when calling rand() directly. If you call rand() directly without assigning new seed every time you will get same value all the time.

A-K Over a year ago

Which is exactly identical to what I posted long ago: sqlserver-tips.blogspot.com/2006/09/…

Charles Bretana · Accepted Answer · 2008-11-07 00:48:08Z

1

Use the Rand() function.... and seed it with something else random like the number of millesconds in the current sysDate or current timestamp... Or a call to NewId() function...

answered Nov 7, 2008 at 0:48

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

Comments

Cristian Libardo · Accepted Answer · 2008-11-09 22:34:07Z

0

Not the full-alphabet-randomness you have but kind of random:

select substring(replace(newid(),'-',''),0,10)

Edit: I learned from the comments that newid() isn't very good for randomness, especially in combination with substring.

edited Nov 9, 2008 at 22:34

answered Nov 6, 2008 at 16:59

Cristian Libardo

9,2683 gold badges38 silver badges41 bronze badges

3 Comments

user12861 Over a year ago

newid() is not very random at all. There can be a lot of structure, depending on the algorithm used.

Cristian Libardo Over a year ago

Interesting, do you know which algorithm sql server uses?

user12861 Over a year ago

Sorry, I don't know exactly which algorithm is used. But there's a great article here: blogs.msdn.com/oldnewthing/archive/2008/06/27/8659071.aspx with a lot of info on the huge amount of structure in GUIDs for one algorithm.

gngolakia · Accepted Answer · 2011-11-27 11:44:22Z

0

Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user.

The following stored procedure creates strings of random characters based on four parameters that configure the result.

> create proc [dbo].uspRandChars
>     @len int,
>     @min tinyint = 48,
>     @range tinyint = 74,
>     @exclude varchar(50) = '0:;<=>?@O[]`^\/',
>     @output varchar(50) output as 
>     declare @char char
>     set @output = ''
>  
>     while @len > 0 begin
>        select @char = char(round(rand() * @range + @min, 0))
>        if charindex(@char, @exclude) = 0 begin
>            set @output += @char
>            set @len = @len - 1
>        end
>     end

answered Nov 27, 2011 at 11:44

gngolakia

2,2261 gold badge19 silver badges13 bronze badges

Collectives™ on Stack Overflow

Generate Random values from SQL

6 Answers 6

1 Comment

Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related