Clever Geek Handbook

Generate random values ​​from SQL

It looks like SQL Server, like most other Random Function products, is really not 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'm sure 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


source to share


6 answers


Most programmers make the mistake of rethinking the functionality of randomization and ending up with not being random at all. I recommend that you stick with the built-in RAND () function. Place it once, then select as many values ​​as you need.



+9


source


Reimagining RAND is a recipe for disaster. Where have you ever noticed that he misbehaves? I don't think you even need to sow it. SQL Server has to seed it by itself. Seeding should be necessary when you need to produce the same "random" sequence several times when testing algorithms or some of them.



+3


source


As per the on-line book for the rand () function: if no seed is specified, the Microsoft SQL Server 2005 Database Engine will randomly seed. For a given initial value, the return result is always the same.

You can avoid this with a quick and dirty trick:

  • Create a view like this:

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

  • Then create a 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

This way you have a random seed every time you call the wrapped_rand () function and an arbitrary random value between 0 and 1.

+1


source


Use the Rand () function ... and populate it with something else random, such as the number of milliseconds in the current sysDate or current timestamp ... Or a call to NewId () ...

+1


source


Not a complete alphabet - the randomness you have, but random:

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

Edit: I learned from the comments that newid () is not very good for randomness, especially when combined with a substring.

0


source


Sometimes it is required to reset the password using a temporary password or generate a random password for a new user.

The following stored procedure generates random character strings based on four parameters that customize 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
0


source






More articles:


All Articles