December 24, 2020 at 12:00 am
Comments posted to this topic are about the item Merry Christmas 2020
December 24, 2020 at 2:36 pm
A nice easy one for Christmas!
Merry Christmas to you and yours as well! And thanks for all the great QOTD's!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 24, 2020 at 2:44 pm
I am curious why, given that RAND() is supposed to return random numbers, it always returns the same "random" numbers for the same query?. Is that by design for consistent testing with the same values or something?
December 24, 2020 at 3:11 pm
If memory serves, it is because RAND() is only semi-random. You need to provide a "seed" which will be used to make it (again) semi random.
RAND() returns a consistent list of values for a given seed. I remember back in the QBASIC days, I'd use the current time as my seed as it was never going to be the same number twice so it resulted in a more random set of numbers.
RAND() in TSQL takes 1 argument which would be the seed. If none is provided, I am not sure what it uses, but want to see some fun? Try running this query:
SELECT RAND(), RAND(11)
and note the value from RAND(). Now run it again and the value of RAND() will change, but the value of RAND(11) will remain. If you change the 11 to any other value, the same results are apparent. Strange, no?
Now, to get a new random number per second, you can use this (there may be a more efficient way to do this, but it was the route my brain went):
SELECT RAND(CAST(CAST(REPLACE(CAST(CAST(GETDATE() AS TIME) AS VARCHAR(MAX)),':','') AS FLOAT) AS INT))
which will give you a new random number ever second. But run it twice in a second and you get the same number.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 24, 2020 at 3:53 pm
As Brian mentioned, a seed helps here, which often is good since you may have a way to pass in a seed from a value in a row. However, RAND() also only runs one for a query, so you get the same (semi) random number in all rows. A different run could get you something different, but it's not a great randomizer, like for a random ORDER BY. NEWID() works better.
December 24, 2020 at 8:15 pm
I am curious why, given that RAND() is supposed to return random numbers, it always returns the same "random" numbers for the same query?. Is that by design for consistent testing with the same values or something?
Heh... it's simply because it was written that way so very long ago. Apparently the designers of the function never expected folks to use it for more than 1 row at a time.
The NEWID() function is the only function that will return a "random" (actually, it's a pseudo-random value) value. It also cannot be used directly by RAND(). It needs to be converted to something else first.
One of the fastest methods I've ever seen to do such a conversion is to use CHECKSUM() to convert the random GUID that NEWID() produces into a seemingly random INT.
So, for example, if you wanted to return the random output of a million RAND()'s (0 <= X < 1 is the range of values for RAND()) , you can do the following...
SELECT TOP (1000000)
RandFloat = RAND(CHECKSUM(NEWID()))
FROM sys.all_columns ac1 --The CROSS JOIN creates a "pseudo-cursor" loop as a "row source"
CROSS JOIN sys.all_columns ac2 --and is "Nasty Fast"
;
Just in case you ever need to, you can generate random range constrained data of just about any type. The following articles explain how to do that. I do (someday) need to update the article to make a small fix on the INT side of the world because it is possible that that most negative number can be generated and there is no positive number equivalent. When I say it "is possible", I know that from personal experience because I've had it happen to me.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply