Random sampling of rows from a table in SQL Server page 1
In due time we used random sampling of entries to create a list of questions for a test. We did it on the client with the help of basic language functions which generate pseudorandom numbers within a given range (for example, the RND function in Visual Basic).
It turned out, however, quite simple to arrange it on the server side. Both by similar tools (with the help of the RAND function in T-SQL) and on the basis of uniqueidentifier datatype which is called global unique udentifier and lools like:
For the solution of our problem it is essential that this unique identifier can be automatically generated by employing the NEWID function. Just write down in QA (Query Analyzer) or in MS (Management Studio)
So, the idea is to add a computed column NEWID, by which the sorting should be performed, to the query yielding the required number of entries. Since generated values are arbitrary, we'll get a random fetch.
Assume that we need to select two random models from the Product table:
Let us run this query several times:
You can carry on if you like.
Now let us come back to the "traditional" way. The RAND function generates pseudorandom floating point numbers between 0 and 1.
The term 'pseudorandom' denotes that such numbers are computed by means of a certain atithmetic algorythm. That is, with the initial conditions (input) being equal, the derived numbers will be the same. The initial conditions may be set explicitly, by supplying the function argument which can be any number of tinyint, int or smallint type, or implicitly. In the last case the argument is omitted and the initial value is selected by SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server.
Let us execute the following query:
We will run it in MS. I got 0.0485421339242268 and 0.72009490018203537. It may be safely stated that the first number you get will be different, but the second may be the same, since in this case the initial value was specified (350).