I have a table with a lot of rows. It is indexed. One of the operations that I'm routinely doing is selecting a random record from this table. To do this, I use following SQL statement:
SELECT TOP 1 *
FROM
( SELECT TOP (@RecNo) * FROM Table ORDER BY Date ASC ) AS subquery1
ORDER BY
Date DESC ;
Where @RecNo is the random number. Query takes annoyingly lot of time to run. Any ideas what could be optimized here?