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
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.
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.
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.