2

My question is to whether the following is reliable on a large dataset in MSSQL:

select top 1 * from table order by row_id

I was wondering if there was some threshold that when met will return the top 1 from a set of data. For example if there was a table with millions of rows and the query above was ran if it was reliable that the row returned would indeed be the top row or if it would be top row of a subset of that data.

Maxqueue
  • 2,194
  • 2
  • 23
  • 55
  • It will indeed be the top 1 row from the data that is returned. – Jacob H Jun 08 '17 at 20:35
  • It will return only 1 row in any case if it has records – Kannan Kandasamy Jun 08 '17 at 20:36
  • 1
    You should check out [this answer](https://dba.stackexchange.com/a/171443/95107) from another quesiton... specifically that you [must use order by for your query to be deterministic](https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/) as you have done which will ensure that you will get the `TOP 1` row based on your `ORDER BY` regardless if there are 2 records or 2 million. – S3S Jun 08 '17 at 20:55

2 Answers2

2

As long as there is an 'order by' clause, it's both consistent and reliable.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33
0

You can control based on variable as below:

declare @i int = 1
select top(@i) * from NumSeq order by col1

Basedon variable @i it will return that number of records

Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38