Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Random problems


Random problems

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
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 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
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 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
vzurkowski
vzurkowski
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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 ?)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
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 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
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
        Winkd
  GROUP BY d.N
  ORDER BY d.N


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike C
Mike C
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 1168
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. Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
Here's a simple test for the floating point stuff. Works fine, fails safe, and drains to the bilge. :-P

 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
        Winkd
  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 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: Administrators
Points: 41214 Visits: 18876
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
My Blog: www.voiceofthedba.com
woolfson
woolfson
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53110 Visits: 40376
woolfson (6/9/2009)
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.


Ummmm... no. Randomness <> Uniqueness. Unless you're talking about just using NEWID(), that is.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search