Random number generator

Question

Post reply

Random number generator

sudrav

SSC-Addicted

Points: 424
More actions
October 15, 2008 at 11:30 am

#194656

Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say random number and store it in the table
1. I want to generate a random number for each row and store it in the table
2. I do not want to write a seperate function and call it. I want to do it in one step .is it possible ?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

What range and what datatype?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Oh, and "how" random? Linear congruential differentiator random? Cryptographically strong random? More? Or less? Or in-between?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

sudrav (10/15/2008)
Ok.Guys I need your help. I have a table of about 60000 records. I want to add a column say random number and store it in the table
1. I want to generate a random number for each row and store it in the table
2. I do not want to write a seperate function and call it. I want to do it in one step .is it possible ?

Pick one...

DROP TABLE JBMTest

GO

--===== Create and populate a 1,000,000 row test table.

-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers

-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers

-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings

-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers

-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times

-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'

-- for all rows.

-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)

-- Jeff Moden

SELECT TOP 1000000

RowNum = IDENTITY(INT,1,1),

SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),

SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),

SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),

SomeHex12 = RIGHT(NEWID(),12)

INTO dbo.JBMTest

FROM Master.dbo.SysColumns t1

CROSS JOIN Master.dbo.SysColumns t2

--===== A table is not properly formed unless a Primary Key has been assigned

ALTER TABLE dbo.JBMTest

ADD PRIMARY KEY CLUSTERED (RowNum)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

So that's in-between LGD and Cryptographic? And I don't see any varbinary(MAX) or sql_variant data-types. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

rbarryyoung (10/15/2008)
So that's in-between LGD and Cryptographic? And I don't see any varbinary(MAX) or sql_variant data-types. 😀

Heh... no... It's somewhere in between a pork chop and a chicken leg. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff - enquiring minds wanna know - how are you getting your code windows to scroll side to side?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Matt Miller (10/15/2008)
Jeff - enquiring minds wanna know - how are you getting your code windows to scroll side to side?

I think Steve's in-house Ninja tweeked the forum code for it very recently...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

battula.praveen Old Hand Points: 390 More actions · Answer 8

See this small example and check that helps.

http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

battula.praveen (3/29/2010)
See this small example and check that helps.
http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html

The only problem with that is that it uses RAND() alone and is good for only one row at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cindy Conway-312336 SSC-Addicted Points: 466 More actions · Answer 10

I've created a CLR TVF to handle this. I find C# easier to use than t-SQL for random numbers within a range. My TVF returns a uniqueKey along with the random numbers, so I just join the rowNumber() with the uniqueKey. The update statement ends up looking like this. Here is how to create the TVF that generated the random numbers.

;WITH clientCTE AS

(

SELECT

ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',

randomNumber

FROM

client

)

UPDATE clientCTE

SET randomNumber = rn.RandomNumber

FROM dbo.tvfRandomNumberList(1,5,1000) AS rn

WHERE rn.UniqueKey = clientCTE.RowNumber

www.sqlwithcindy.com

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

Gosh - didn't see the date ! sorry!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

mister.magoo (4/25/2013)
Gosh - didn't see the date ! sorry!

Don't let dates of posts scare you away. What did you have to say?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

Cindy Conway-312336 (4/25/2013)
I've created a CLR TVF to handle this. I find C# easier to use than t-SQL for random numbers within a range. My TVF returns a uniqueKey along with the random numbers, so I just join the rowNumber() with the uniqueKey. The update statement ends up looking like this. Here is how to create the TVF that generated the random numbers.
;WITH clientCTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',
randomNumber
FROM
client
)
UPDATE clientCTE
SET randomNumber = rn.RandomNumber
FROM dbo.tvfRandomNumberList(1,5,1000) AS rn
WHERE rn.UniqueKey = clientCTE.RowNumber

Hi Cindy,

Nice code.

Random numbers just aren't that difficult to handle in T-SQL. With just a little of the "Black Arts", it's easy to create a direct replacement for your fine CLR using only T-SQL. Here's the code to do such a thing. I've kept on with your naming convention and shamelessly borrowed the header comments from your CLR. Its performance is probably second only to a CLR such as yours.

CREATE VIEW dbo.vUnconstrainedRandomInt WITH SCHEMABINDING AS

/***************************************************************************************************

Returns an unconstrained random integer suitable for use virtually anywhere including in a function

***************************************************************************************************/

SELECT UnconstrainedRandomInt = ABS(CHECKSUM(NEWID()));

GO

CREATE FUNCTION dbo.tvfRandomNumberList

/***************************************************************************************************

Function returns a list of randomly generated numbers with a UniqueKey.

@pMinValue = the mimiumn value of the random numbers generated.

@pMaxValue = the maximum value of the random numbers generated.

@pSize = the number of random numbers to be returned.

This code is a direct replacement for the CLR function which may be found at the following URL.

http://www.sqlwithcindy.com/2013/04/elegant-random-number-list-in-sql-server.html

***************************************************************************************************/

--===== Declare the I/O for this function

(

@pMinValue INT

, @pMaxValue INT

, @pSize INT

)

RETURNS TABLE WITH SCHEMABINDING AS

--===== Create and return the random number list

RETURN WITH

E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10E1 or up to 10 rows

)

, E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10E3 or up to 1,000 rows

, E9(N) AS (SELECT 1 FROM E3 a, E3 b, E3 c) --10E9 or up to 1,000,000,000 rows

SELECT TOP (@pSize)

UniqueKey = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

, RandomNumber = ri.UnconstrainedRandomInt %(@pMaxValue-@pMinValue+1) +@pMinValue

FROM E9, dbo.vUnconstrainedRandomInt ri

;

The truth be told, though, the generation of random numbers don't need such sophistication as an iTVF. For example, you wrote the following code to assign random numbers to the "Client" table.

;WITH clientCTE AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY int_ID) AS 'RowNumber',
randomNumber
FROM
client
)
UPDATE clientCTE
SET randomNumber = rn.RandomNumber
FROM dbo.tvfRandomNumberList(1,5,1000) AS rn
WHERE rn.UniqueKey = clientCTE.RowNumber

Again, using some of the "Black Arts" available in T-SQL, the code can be greatly simplified and the expense of a join can be avoided thusly...

UPDATE dbo.Client

SET RandomNumber = ABS(CHECKSUM(NEWID())) %5 +1

;

For more information on the generation of random Integers, random Floats, and random Dates with or without Times, please see the following articles:

http://www.sqlservercentral.com/articles/Data+Generation/87901/

http://www.sqlservercentral.com/articles/Test+Data/88964/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Jeff Moden (4/25/2013)
mister.magoo (4/25/2013)
Gosh - didn't see the date ! sorry!
Don't let dates of posts scare you away. What did you have to say?

Oh nothing important, just questioning the intent - whenever I see people asking for specific things in a non-specific way it makes me wonder what they really want. In this case, did the OP actually want random numbers assigned to each row - for what purpose? I can't think of any reasonable reason for random numbers being assigned to the rows unless it is for consistent/repeatable "random" ordering or consistent/repeatable "random" selection, and if it is one of those, do they want "random" numbers (with the possibility of duplicates - or even the low possibility of them all being assigned the same number) or unique numbers but randomly assigned ?

However , I spotted that this was another old post being added to randomly and thought there was not much likelihood of the OP still looking for an answer 5 years one, so backed off 😛

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);