Does SQL SERVER supports FIRST_ROWS feature like oracle does?

Question

We known that Oracle support a optimizer mode called FIRST_ROWS ，I want known whether SQL Server has some thing like this. Meaning what should I do if I want to get the first row of a select statement as soon as possible while not waiting it to complete. If it has, can I use it in database drivers like ODBC and may be ADO components?

Answer 1

Yes, it does. You can use the FAST query hint, which gives you the first x rows of a query as fast as possible, to achieve this. Have a look at this example code:

SELECT        whatever
FROM          YourTable
OPTION (FAST 1)

You can also use the FASTFIRSTROW table hint:

SELECT        whatever
FROM          YourTable
WITH (FASTFIRSTROW)

Answer 2

Like TOP 1 ?

There is also optimizer hint: FAST number_rows. (Query Hints)

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.