Random number generator

Question

Post reply

Random number generator

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]

Jeff Moden SSC Guru Points: 1004353 More actions · Answer 1

Heh... got it and understood. It was a really open ended original question.

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

Hi MM,

I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.

www.sqlwithcindy.com

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

Cindy Conway-312336 (4/26/2013)
Hi MM,
I was assigning random numbers to each row because I was generating test data. In my case, I was simulating data returned from Market Research Surveys. The questions use a scale from 1 to 5 or 1 to 10. I wanted to randomly alot ratings to my test data.

See, that's why I'm not a mind reader, I would never have guessed that!

Good to know anyway, thanks 😀

MM

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

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

Hi Jeff,

Nice code! Clever how you did that. I knew that it could be done in t-sql (although I don't think I would have come up with a function as clever as yours). I like C# for things like random numbers and complex string manipulation. The functionality built in is more elegant IMO. The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers. Like you said, performance is pretty equal. The CLR outperforms on very big data sets (1million+), but only by a small amount .

Anyone who reads this post gets to choose the option best suited to their particular situation.

www.sqlwithcindy.com

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

Cindy Conway-312336 (4/26/2013)
The simple update statement you listed is great too, but it looses the ability to specify a lower and upper range to the random numbers.

Thanks for the feedback, Cindy. However, not quite true on the upper and lower range of random numbers (integers, in this case). It uses the same basic formula that a lot of random number generators use to specify the domain to be returned. If you look carefully at the function, it takes min and max values just like your CLR function does.

--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: 1004353 More actions · Answer 6

Ah, apologies. I didn't understand what you were getting at. I see what you're saying now.

The singleton formula can be easily modified to take min/max parameters as follows using the classic domain range calculations for random numbers.

DECLARE @pMinRandom INT,

@pMaxRandom INT

;

UPDATE dbo.Client

SET RandomNumber = ABS(CHECKSUM(NEWID())) %(@pMaxRandom-@pMinRandom+1) +@pMiRandom

;

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