SQL TOP statement, how to make sure it's "random"?

Question

I was studying SQL where I encountered a TOP statement. i.e.

SELECT TOP 2 * FROM Persons 

select at most 2 row from table Persons.

However, if I were to use it for statistical reason, I'd like such process to be random: i.e. independent of time and order in the table.

My questions was that:

1. How to make sure TOP statement was totally "random"(it's selection was not ordered)? Further, how to make sure TOP statement was ordered with respect to some variable( i.e. age)

2. How did the TOP statement actually work?

Answer 1

I'm going to assume you're using SQL Server, as that's the version of SQL I know of that uses TOP rather than LIMIT.

Data in SQL Server is always in some sort of order; however, what that order is can only be guaranteed when you include an ORDER BY clause in your SQL statement.

Without an ORDER BY clause, the order of a dataset is determined by the process required to generate it, and the order in which is was stored.

If a table doesn't change, and you run the same SQL statement against it multiple times, you'll get the data back in the same order. That's because the query engine is applying the same process each time to generate the resulting data set.

So, if you were to issue your query against a table (with no inserts, updates, or deletes) multiple times, you'll get the same 2 rows each time.

Note: depending on the structure of the table (for instance, whether or not there's a clustered index), and activity against the table, your SELECT TOP 2 * might give different results on a table that's not basically static. That's why you need an ORDER BY to guarantee the order, even if today a query run without an ORDER BY returns the data in the order you want it consistently.

With TOP and ORDER BY, the query engine must at least partially sort your data (if it can locate and sort the top n rows, it doesn't have to sort the rest). With TOP but not ORDER BY, it simply selects the first n rows of the dataset it's generated.

If you want a random ordering, you (strange as it would sound) need to order the data by something random. ORDER BY NEWID() (as suggested in the comments by Lamak) would do this. NEWID() generate a new GUID each time it runs, and those are not generated in any sort of sequential order. This SO question explains how that works. It also describes how TOP works further.

As sp_BlitzErik points out, ORDER BY NEWID() is not fast, and is slower the larger the table is (it has to generate a GUID for each row in the table, regardless of the TOP number of rows to be returned). He links to How to Get a Random Row from a Large Table, an article on his company's website, for additional suggestions for getting truly random rows from a table.

Basically, the options are to use a relatively obscure query option called TABLESAMPLE, which can present problems; or, use the RAND function to generate a random ID value to return one row (dumping random numbers into a temp table if you need more than one or two rows, or a variable number of rows each time you run). You can also use RAND with the OFFSET ... FETCH clause to get a single random row in SQL 2012 or later.