<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Chris Nowicki
»
Random problems
58 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
Random problems
Rate Topic
Display Mode
Topic Options
Author
Message
Chad Crawford
Chad Crawford
Posted Tuesday, June 09, 2009 7:46 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 2,550,
Visits: 17,368
This is all I have to say about randomness:
Dilbert's Random Number Generator
:)
Chad
Post #731441
Jeff Moden
Jeff Moden
Posted Tuesday, June 09, 2009 8:45 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
charles.flock (6/9/2009)
The XLeratorDB function library inlcudes a RANDBETWEEN scalar function which works just like the EXCEL RANDBETWEEN function. You could have just included RANDBETWEEN(1, 5) in your select and you would have gotten the results you wanted. You can find out more about this function at http://www.westclintech.com/Documentation/XLeratorDBmathDocumentation/tabid/140/topic/RANDBETWEEN/Default.aspx
Heh... why would you buy such a thing when it's so easy to pull off in T-SQL? And where's the mayo? Spam always goes better with mayo.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #731504
Jeff Moden
Jeff Moden
Posted Tuesday, June 09, 2009 8:50 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
Peso (6/9/2009)
And it's also well established that
ABS(CHECKSUM(NEWID())) % 100
gives random numbers between 0 and 99 with very high, good and even distribution.
I used to do the coversion using Varbinary and Int... Peso showed me the above (several years ago, now) and it's comparatively very fast compared to the way I used to do it. It's also easy to remember.
For random floating point numbers from 0 to almost 100 (it'll never reach 100 because 0<=RAND()<1), a very slight change is all that's required...
RAND(CHECKSUM(NEWID())) * 100
Of course, you can add a "starting" number to either formula to get ranges of random data that start at something other than 0.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #731511
vzurkowski
vzurkowski
Posted Tuesday, June 09, 2009 8:51 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, June 15, 2009 7:50 AM
Points: 2,
Visits: 19
To "chose at random from 1,..,5" usually means to chose from 1,..,5 in such a way that any outcome has the same probability of being selected.
Assuming T-SQL RAND() has good statistical properties (if someone knows of tests, please let me know), why would RAND(CHECKSUM(NEWID()) have good properties? (i.e.: why would it select a number uniformly distributed between 0 and 1 ?)
Post #731513
Jeff Moden
Jeff Moden
Posted Tuesday, June 09, 2009 8:53 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
vzurkowski (6/9/2009)
To "chose at random from 1,..,5" usually means to chose from 1,..,5 in such a way that any outcome has the same probability of being selected.
Assuming T-SQL RAND() has good statistical properties (if someone knows of tests, please let me know), why would RAND(CHECKSUM(NEWID()) have good properties? (i.e.: why would it select a number uniformly distributed between 0 and 1 ?)
Why wouldn't it? You're giving it a random seed.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #731517
Jeff Moden
Jeff Moden
Posted Tuesday, June 09, 2009 9:06 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
Jeff Moden (6/9/2009)
vzurkowski (6/9/2009)
To "chose at random from 1,..,5" usually means to chose from 1,..,5 in such a way that any outcome has the same probability of being selected.
Assuming T-SQL RAND() has good statistical properties (if someone knows of tests, please let me know), why would RAND(CHECKSUM(NEWID()) have good properties? (i.e.: why would it select a number uniformly distributed between 0 and 1 ?)
Why wouldn't it? You're giving it a random seed.
In fact, test it yourself. The following produces what would be expected of a random number generator...
SELECT d.N, COUNT(*)
FROM (
SELECT TOP 1000000
ABS(CHECKSUM(NEWID())) % 5 + 1 AS N
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
)d
GROUP BY d.N
ORDER BY d.N
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #731528
Mike C
Mike C
Posted Tuesday, June 09, 2009 9:14 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276,
Visits: 1,112
Here's an interesting twist:
SELECT TOP (100) ABS(CHECKSUM(NEWID())) % 100
FROM dbo.syscolumns
WHERE ABS(CHECKSUM(NEWID())) % 100 BETWEEN 1 AND 5
Notice that the values returned are way outside the range 1..5 since the NEWID() function generates a new unique identifier every time it's invoked, in both the SELECT clause and the WHERE clause of the same statement. :)
Post #731540
Jeff Moden
Jeff Moden
Posted Tuesday, June 09, 2009 9:15 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910,
Visits: 26,800
Here's a simple test for the floating point stuff. Works fine, fails safe, and drains to the bilge.
SELECT STR(FLOOR(d.N*100)/100,4,2), COUNT(*)
FROM (
SELECT TOP 1000000
RAND(CHECKSUM(NEWID())) * 5 AS N
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2
)d
GROUP BY STR(FLOOR(d.N*100)/100,4,2)
ORDER BY STR(FLOOR(d.N*100)/100,4,2)
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #731542
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, June 09, 2009 9:20 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433,
Visits: 13,745
The formatting is fixed, or should be. Please let me know if it is still messed up.
I have seen some strangeness with randomness in SQL Server, running the same query on restart, etc. However NewID() seems to work well.
Follow me on Twitter:
@way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
Post #731545
woolfson
woolfson
Posted Tuesday, June 09, 2009 9:29 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, May 18, 2012 3:41 PM
Points: 1,
Visits: 2
This is brilliant; I have always struggled with providing a unique identifier to a set of data after the data has already been written to the table, and it's always been a challenge, especially when doing it within SQL. Well, good job, very good job. Thanks.
Post #731555
« Prev Topic
|
Next Topic »
58 posts, Page 2 of 6
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
