Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Random problems Expand / Collapse
Author
Message
Posted Tuesday, June 9, 2009 5:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
drogers (6/9/2009)

A minor quibble with the article: the problem is not that RAND() is called multiple times with the same seed, once per row. The problem is that RAND() is only called once per query (just like GETDATE()).



Another minor quibble - each instance of RAND() is invoked once per query.

SELECT RAND(), RAND()

Generates two different random numbers because RAND() is actually called twice. Each RAND() result is repeated for every row of the result set (in this case only one row).
Post #731955
Posted Tuesday, June 9, 2009 6:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
drogers (6/9/2009)
Jeff Moden (6/9/2009)


RAND() in T-SQL works exactly the same way as RAND() in any computer language.... one row or value at a time.


No, it doesn't work at all like most languages. Most people expect RAND() in the result set to generate multiple values when the result set contains multiple rows. It doesn't. In my experience, this functionality is pretty unique.

A minor quibble with the article: the problem is not that RAND() is called multiple times with the same seed, once per row. The problem is that RAND() is only called once per query (just like GETDATE()). Adding the NEWID() call is a hack that forces the query optimizer to call RAND() once per row.

Great article!

David



Stop and think about it... in every other language, you only have control over one row at a time. In declarative languages like T-SQL, it's a set. If you use RAND() in a cursor like you would in C# or any other language, you are affecting only one row at a time.

That's not to say that I agree with the idea that RAND() doesn't work like NEWID() in T-SQL.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #731966
Posted Tuesday, June 9, 2009 7:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
Mike C (6/9/2009)
drogers (6/9/2009)

A minor quibble with the article: the problem is not that RAND() is called multiple times with the same seed, once per row. The problem is that RAND() is only called once per query (just like GETDATE()).



Another minor quibble - each instance of RAND() is invoked once per query.

SELECT RAND(), RAND()

Generates two different random numbers because RAND() is actually called twice. Each RAND() result is repeated for every row of the result set (in this case only one row).


Heh... correct identification of the problem in 90% of the solution. We know the behavior and the work around. Use the work around.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #731968
Posted Tuesday, June 9, 2009 10:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Jeff Moden (6/9/2009)
Mike C (6/9/2009)


Another minor quibble - each instance of RAND() is invoked once per query.

SELECT RAND(), RAND()

Generates two different random numbers because RAND() is actually called twice. Each RAND() result is repeated for every row of the result set (in this case only one row).


Heh... correct identification of the problem in 90% of the solution. We know the behavior and the work around. Use the work around.


LOL Jeff. I wrote a chapter about 4 months ago that uses the CHECKSUM(NEWID()) method to generate "random" test data.
Post #732017
Posted Tuesday, June 9, 2009 10:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
Mike C (6/9/2009)
Jeff Moden (6/9/2009)
Mike C (6/9/2009)


Another minor quibble - each instance of RAND() is invoked once per query.

SELECT RAND(), RAND()

Generates two different random numbers because RAND() is actually called twice. Each RAND() result is repeated for every row of the result set (in this case only one row).


Heh... correct identification of the problem in 90% of the solution. We know the behavior and the work around. Use the work around.


LOL Jeff. I wrote a chapter about 4 months ago that uses the CHECKSUM(NEWID()) method to generate "random" test data.


Dang... sorry Mike. Based on what you wrote, I thought you were going to be one of those folks that didn't get it. After having read a book of yours, I was really surprised at you response here. I took it the wrong way.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #732026
Posted Wednesday, June 10, 2009 8:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 4:41 PM
Points: 19, Visits: 101

It really is this simple:

SELECT InvoiceID FROM Invoice ORDER BY NEWID()

Post #732312
Posted Wednesday, June 10, 2009 8:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
Jeff Moden (6/9/2009)
Mike C (6/9/2009)
Jeff Moden (6/9/2009)
Mike C (6/9/2009)


Another minor quibble - each instance of RAND() is invoked once per query.

SELECT RAND(), RAND()

Generates two different random numbers because RAND() is actually called twice. Each RAND() result is repeated for every row of the result set (in this case only one row).


Heh... correct identification of the problem in 90% of the solution. We know the behavior and the work around. Use the work around.


LOL Jeff. I wrote a chapter about 4 months ago that uses the CHECKSUM(NEWID()) method to generate "random" test data.


Dang... sorry Mike. Based on what you wrote, I thought you were going to be one of those folks that didn't get it. After having read a book of yours, I was really surprised at you response here. I took it the wrong way.


Nope, just a minor quibble with the description of RAND() function behavior.
Post #732315
Posted Wednesday, June 10, 2009 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 3:02 PM
Points: 3, Visits: 27
Jeff Moden (6/9/2009)
drogers (6/9/2009)
Jeff Moden (6/9/2009)


RAND() in T-SQL works exactly the same way as RAND() in any computer language.... one row or value at a time.


No, it doesn't work at all like most languages. Most people expect RAND() in the result set to generate multiple values when the result set contains multiple rows. It doesn't. In my experience, this functionality is pretty unique.


Stop and think about it... in every other language, you only have control over one row at a time. In declarative languages like T-SQL, it's a set. If you use RAND() in a cursor like you would in C# or any other language, you are affecting only one row at a time.

That's not to say that I agree with the idea that RAND() doesn't work like NEWID() in T-SQL.

Uh, why would you "not agree with the idea that RAND() doesn't work like NEWID() in T-SQL." Do you find the discrepancy "unexpected?"
Post #732462
Posted Wednesday, June 10, 2009 11:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 35,265, Visits: 31,754
drogers (6/10/2009)

Uh, why would you "not agree with the idea that RAND() doesn't work like NEWID() in T-SQL." Do you find the discrepancy "unexpected?"


Heh... Only the first time I ran across it.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #732492
Posted Wednesday, June 10, 2009 8:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:23 AM
Points: 20, Visits: 16
I like this solution - it is simpler than mine.
Post #732723
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse