locked
is it okay to use NEWID() for random rows more than 500 RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    I am using MSSQL 2012

    I have database tables with n numbers of of questions and answers for a online exam form.

    Suppose I need to make a question paper set of 5 questions and I am using the below query for a student for a random no or 20 questions set.

    The requirement is that the questions set should not be repeat but yes questions may repeat.

    I wrote the below query, it is giving a set of 5 questions, I just want to confirm it will be work same as for 500 question set? or it is having some limitations?

    SELECT TOP 5 question FROM tbl_question_details
    ORDER BY NEWID() -- Sql server 2012
    
    Thanks

    Wednesday, March 14, 2018 5:18 AM

Answers

  • User-1716253493 posted

    AFAIK, newid() in this case is a temp column.

    The logic is create new column with newid() values then sort the result by this column.

    it will be work same as for 500 question set? Yes

    I have test it with more than 100000 records like this

    SELECT col1, col2, newid() from table1
    SELECT col1, col2 from table1 order by newid()

    the id containing many character combination ie : 55836b22-b1d9-4c70-8c1d-577522883355

    Don't worry about limitation, it can create very hug posible unique values

    But, server will generate the values on the fly. Maybe the problem is performance when sorting hug datas, i never compare it.

    I test it with top(10) from more than 100000 records, the result is fast

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 7:07 AM

All replies

  • User-1716253493 posted

    AFAIK, newid() in this case is a temp column.

    The logic is create new column with newid() values then sort the result by this column.

    it will be work same as for 500 question set? Yes

    I have test it with more than 100000 records like this

    SELECT col1, col2, newid() from table1
    SELECT col1, col2 from table1 order by newid()

    the id containing many character combination ie : 55836b22-b1d9-4c70-8c1d-577522883355

    Don't worry about limitation, it can create very hug posible unique values

    But, server will generate the values on the fly. Maybe the problem is performance when sorting hug datas, i never compare it.

    I test it with top(10) from more than 100000 records, the result is fast

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 14, 2018 7:07 AM
  • User753101303 posted

    Hi,

    It works regardless of how many rows you have. Not sure to get which issue you anticipate :

    questions set should not be repeat but yes questions may repeat

    Do you mean the table could have multiple rows with the same question ? In this case it would be best to clean up the data.

    Wednesday, March 14, 2018 8:12 AM
  • User1052662409 posted

    PatriceSc

    Do you mean the table could have multiple rows with the same question ?

    '

    No, in fact every row is with unique question and I want those unique set for every user_id i.e. 2 students may have some of same questions in their sets but can not have same set of questions

    Wednesday, March 14, 2018 8:47 AM