September 24, 2014 at 9:56 pm
Comments posted to this topic are about the item Random values
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2014 at 10:04 pm
Mmm, I just ran the code using SQL 2012 and I get 10 different result. Each of the records uses a different seed and, hence, should produce a different result.
The query is equivalent to something like
SELECT RAND(1) UNION SELECT RAND(2) .... UNION SELECT RAND(10).
So, I reckon that the answer of "1, every value is the same" is actually incorrect.
September 24, 2014 at 10:08 pm
SQL 2000: Error 'Row_Number' is not a recognized function name.
SQL 2005 to 2012: 10 distinct rows returned, answer should be C.
September 24, 2014 at 10:15 pm
is it for single time execution or more than 1 time execution?
if 1 time it will give all distinct values, if more than once will give same as 1st result.
Thanks,
Shiva N
Database Consultant
September 24, 2014 at 10:23 pm
Arrggg, wrong box checked.
10 is correct. I'll award back points tomorrow.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2014 at 10:23 pm
Andrew G (9/24/2014)
SQL 2000: Error 'Row_Number' is not a recognized function name.SQL 2005 to 2012: 10 distinct rows returned, answer should be C.
you should never expect any of our questions to work on 2000. Some might, but we'll never test there.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 24, 2014 at 11:26 pm
Shiva you are right.
Good observation...:-)
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature
September 25, 2014 at 1:25 am
Great question Steve!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2014 at 3:04 am
Can you explain why you aliased the sys.syscolumns table?
SELECT TOP 10
RAND( ROW_NUMBER() OVER (ORDER BY id))
FROM sys.syscolumns
is functionally identical, as far as I am aware.
September 25, 2014 at 3:17 am
This was removed by the editor as SPAM
September 25, 2014 at 5:06 am
Steve Jones - SSC Editor (9/24/2014)
Arrggg, wrong box checked.10 is correct. I'll award back points tomorrow.
-1 errrrrr :angry:
September 25, 2014 at 5:08 am
Shiva N (9/24/2014)
is it for single time execution or more than 1 time execution?if 1 time it will give all distinct values, if more than once will give same as 1st result.
Good catch.
September 25, 2014 at 5:41 am
I thought that was a good question. It showed a different way of calling the random function. Thank you for it.
September 25, 2014 at 6:07 am
Good question, but two small flaws:
1: the explanation is misleading. It says "The RAND function only produces one value for all calls in a specific connection with a specific seed" which would tend to make people think that a given seed might produce different values in different connectipns. It doesn't: the for a given seed the value produced by RAND is alwys the same, regardless of what connection it is called in.
2: the values produced are not random, RAND with a seed is deterministic (as clearly stated in BOL Deterministic
and Nondeterministic Functions) - the value for a given seed is always the same.
Tom
September 25, 2014 at 6:14 am
Thanks for the question, Steve.
I think this is a fairly easy question. The only thing that got me thinking was whether or not it would throw an error.
---------------
Mel. 😎
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply