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';

Answer 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:

1. Create view like this:

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

2. 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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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...