SQL Random selection with NewID()
-
I have seen on some web sites suggesting to use ORDER BY CHECKSUM(NEWID()) instead of using only NEWID() function.
I think it affects the seed of the NewId function to create real random numbers.
I guess it will add some more work to server but believe it will be more random 🙂
-
Eralper (3/11/2010)
I have seen on some web sites suggesting to use ORDER BY CHECKSUM(NEWID()) instead of using only NEWID() function.I think it affects the seed of the NewId function to create real random numbers. I guess it will add some more work to server but believe it will be more random 🙂
No. You are confusing NEWID() with RAND. RAND uses a seed, NEWID() does not.
CHECKSUM(NEWID()) is likely to have a better distribution of pseudo-random numbers, that's all.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Eralper (3/11/2010)
I have seen on some web sites suggesting to use ORDER BY CHECKSUM(NEWID()) instead of using only NEWID() function.I think it affects the seed of the NewId function to create real random numbers.
I guess it will add some more work to server but believe it will be more random 🙂
This will cause things to be LESS random.
Checksum returns an int (32bits) , and UUID is 128 bits.
So by converting to an integer there more collisions hence Less randomness.
-
Hi Dave and Paul,
So do you think it is better to use only NEWID() function?
Although using CheckSum() might result with a fine distribution of numbers.
-
Ive run a quick test and select top(10) from a table of 54.3 million rows,
the checksum version runs in 23.9 seconds where as the plain newid runs in 22.9.
So in terms of performance not much in it.
I would personally use the plain newid() version.
There is no positive reason i can think of to use the checksum here.
-
Eralper (3/11/2010)
So do you think it is better to use only NEWID() function? Although using CheckSum() might result with a fine distribution of numbers.Each has strengths and weaknesses making each suitable for use in different situations.
Summary: IT DEPENDS. 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Dave Ballantyne (3/11/2010)
This will cause things to be LESS random.Checksum returns an int (32bits) , and UUID is 128 bits.
Heh...less random, eh? 😉
:laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
-
Dave Ballantyne (3/11/2010)
What !!!!use a udf ? , (select null) will perform better 😉 😀
For the .NET-challenged, that was C# 😛
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Hi Jeff Moden, Hi Paul White.
I apologize if i provided insuffient information. That said, here's roughly what i want to achieve:
I have 4 tables in my database, one table is from [dbo.aspnet_Users] in the ASPNETDB.MDF. The rest 3 tables are Test, Questions and the UserTest tables.
The Test table contains QuizID, Title, Description, No_to_Display
After registration the user finds a bunch of test i yanked up from the Test table. Each test has its QuizID that is sent to start.aspx?testID=(QuizID).
Now i needed a means of randomizing Question. Earlier i came up with:
Create Procedure [questionSelector]
AS
Declare @QuizID int, @top int
set @top = (select Q. No_to_Display from Quiz as Q)
SELECT Top (@top )
[QuestionID],
[Title],
[Answer1],
[Answer2],
[Answer3],
[Answer4],
[CorrectAnswer],
[QuestionOrder]
FROM [Question]
WHERE ([QuizID] = @QuizID)
ORDER BY NewID()
After attaching the storedProcedure to my DetailsView Datasource from the Questions.aspx Page, i recieved the following error when i Debugged the page:
Msg 512, Level 16, State 1, Procedure questionSelector, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 1014, Level 15, State 1, Procedure questionSelector, Line 6
TOP clause contains an invalid value.
Then i replaced the procedure entire with SCrazy's SQL statement above. This time i ran the statement directly from MSSQL Server, and the Output Was A set of numbers displaying in each column field instead of fetching the questions i already have in the Questions table.
I was hoping to see the table displaying random questions from the QUESTIONS TABLE , like so:
[QuestionID] 1
[Title] Who won the 1998 Soccer world cup?
[Answer1] Brazil
[Answer2] USA
[Answer3] Germany
[Answer4] Malaysia
then next time i try the same test, a different question should be displayed first.
Sorry for the length, i was trying to be as clear as i could
-
SELECT iTVF.*
FROM dbo.Quiz Z
CROSS
APPLY (
SELECT TOP (Z.no_to_display)
Q.QuestionID,
Q.Title,
Q.Answer1,
Q.Answer2,
Q.Answer3,
Q.Answer4,
Q.CorrectAnswer,
Q.QuestionOrder
FROM dbo.Question Q
WHERE Q.QuizId = Z.QuizId
ORDER BY
NEWID()
) iTVF
WHERE Z.QuizID = @QuizID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Hi Paul white,
I've never come across iTVF. what does it mean?
-
shawndidy (3/12/2010)
Hi Paul white,I've never come across iTVF. what does it mean?
It is just an alias for the result returned by APPLY. Just like T1 in SELECT * FROM MyTable T1.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I tried, its not giving me any errors. But its not fetching any data from the Question Table. The dbo.Question has already being polpulated with data. When i run the page, Its displaying a blank DetailsView on the Question.aspx page. i cant figure out why it is.
-
I had to guess a bit.
Post full CREATE TABLE statements for the tables you have, plus INSERT statements to add sample data, and show the EXACT output you would like to see.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply