views:

988

answers:

5

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';
+1  A: 

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.

Aleksandar
This doesn't make sense to me. How is it different from just using rand() directly?
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.
Aleksandar
Which is exactly identical to what I posted long ago:http://sqlserver-tips.blogspot.com/2006/09/populating-column-with-random-values.html
AlexKuznetsov
A: 

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.

Cristian Libardo
newid() is not very random at all. There can be a lot of structure, depending on the algorithm used.
Interesting, do you know which algorithm sql server uses?
Cristian Libardo
Sorry, I don't know exactly which algorithm is used. But there's a great article here: http://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.
+8  A: 

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.

The word "once" in your answer should be large, red, and blinking.
BoltBait
+3  A: 

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.

+1  A: 

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

Charles Bretana