How to retrieve a random records in ASP.NET

Recently i needed to pull some random records from the database. One of the proposed solutions was something like this:

<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyName\MyDBfolder\DBfile.mdb" providername="System.Data.OleDb" selectcommand="SELECT TOP 1 *, Rnd([id]) AS Expr1 FROM Books ORDER BY Rnd([id]) DESC"></asp:sqldatasource>

This works in the query builder but not when you run the application.

The reason that this doesn’t work is because the rnd method will return the same values over and over again, unless the randomize method is called! But there is another solution that will return a different order without calling the randomize method. The rnd method will also return a different number if a diferent parameter is used. So instead of using the ID value as the parameter, we should use a value that is different every time (now()) and for every record ([Id])

SELECT
TOP 1 * FROM Books
ORDER BY Rnd(-10000000*TimeValue(Now())*[id])

MS SQL Server
The above example will work with MS Access. For Microsoft SQL Server you’ll use the NewId() function to return values of data type unique indentifier. These are similar to Windows GUIDs in that the current date/time is used to seed the random value generator. Again, you’ll use the ordinal position of the “random value” column to order the resultset.

SELECT
   TOP 1 CategoryID,
   CategoryName
FROM
   Categories
ORDER BY
   NewID()

Note: This is not going to be a light operation if you have many, many records (say, a few hundred thousand). However, if you have use a WHERE clause to restrict the number of records that you are creating GUIDs for, then SQL Server will apply that before creating the GUIDs. For instances where you wish to return just a single record (rather than, say 5 random records), it may be better to create a static table with sequentially numbered records, and use a random number generator to determine which record should be returned.