Nadya,

When you use NewID() to return a random row, the speed will depend on the size of the table. If you look at the estimated execution plan for the query (Ctrl-L) or the actual execution plan (Hit Ctrl-K before executing the query), you will see that SQL Server scans the entire table, generates a GUID for each row and at the end it selects one row. On large tables, that's a lot overhead to return one row so at some point it may be faster to select Max and Min and randomly generate numbers between the two until you hit a match.

On my server it takes 30 seconds to select a random row from a table with 10,000,000 rows. This can be optimized by changing the query to generate a random value from an indexed column, then SQL Server can scan the index instead of scanning the table. Indexes are typically smaller than tables so SQL Server has to scan fewer data pages. The optimized query looks like this:

Select * from LargeTable WHERE ID =
(Select Top 1 ID from LargeTable
Order By NewID() )

Since the ID column is indexed, SQL Server scans the index instead of the table and it generates a random ID, than the ID is used to do a lookup. The optimized query takes 16 seconds, so it's almost twice as fast.

Roman


>Yes, the idea was to generate random record from the database. This was a question in tek-tips forum and solution for SQL Server is to use NewID() function. The speed is several times better, than using randomize approach. Too bad, that there is no analogue in Access.
>
>Thanks again and hopefully we will see you here again...
>
>How can we use SQL Server inside Access? I don't understand your last sentence.