I was recently working with various SQL Server Hints. After working for a day on various hints, I realize that for one hint, I am not able to come up with good example. The hint is FAST.

Let us look at the definition of the FAST hint from the Book On-Line.

FAST number_rows

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.

Now the question is in what condition this hint can be useful. I have tried so many different combination, I have found this hint does not make much performance difference, infect I did not notice any change in time taken to load the resultset. I noticed that this hint does not change number of the page read to return result. Now when there is difference in performance is expected because if you read the what FAST hint does is that it only returns first few results FAST – which does not mean there will be difference in performance.

I also understand that this hint gives the guidance/suggestions/hint to query optimizer that there are only 100 rows are in expected resultset. This tricking the optimizer to think there are only 100 rows and which (may) lead to render different execution plan than the one which it would have taken in normal case (without hint). Again, not necessarily, this will happen always.

Now if you read above discussion, you will find that basic understanding of the hint is very clear to me but I still feel that I am missing something. Here are my questions:

1) In what condition this hint can be useful? What is the case, when someone want to see first few rows early because my experience suggests that when first few rows are rendered remaining rows are rendered as well.

2) Is there any way application can retrieve the fast fetched rows from SQL Server?

3) Do you use this hint in your application? Why? When? and How?

Here are few examples I have attempted during the my experiment and found there is no difference in execution plan except its estimated number of rows are different leading optimizer think that the cost is less but in reality that is not the case.

USE AdventureWorks
GO
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
---------------------------------------------
-- Table Scan with Fast Hint
SELECT *
FROM Sales.SalesOrderDetail
GO
SELECT *
FROM Sales.SalesOrderDetail OPTION (FAST 100)
GO
---------------------------------------------
-- Table Scan with Where on Index Key
SELECT *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 14
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE OrderQty = 14
OPTION (FAST 100)
GO
---------------------------------------------
-- Table Scan with Where on Index Key
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID < 1000
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID < 1000
OPTION (FAST 100)
GO


Reference: Pinal Dave (http://blog.sqlauthority.com)