What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN:

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.

For me that does not make much sense but basically the query can get the first XXX rows really fast then the rest at normal speed?

The Microsoft Dynamics query that got me thinking on this is:

select pjproj.project,pjproj.project_desc,pjproj.customer,pjproj.cpnyid
from pjproj WITH (NOLOCK)
where project like  '%'
order by project OPTION(FAST 500)

Can anyone explain exactly what this query hint is doing and its advantage over not using it?

A FAST N will tell SQL Server to generate an execution plan with quickly returning the number of rows defined as N.

Note that the estimates will be as per the N since you are telling sql server to retrieve N rows as fast as possible.

e.g. running below query with fast 500:

-- total rows : 19972
 SELECT [BusinessEntityID]
      ,[TotalPurchaseYTD]
      ,[DateFirstPurchase]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[YearlyIncome]
      ,[Gender]
      ,[TotalChildren]
      ,[NumberChildrenAtHome]
      ,[Education]
      ,[Occupation]
      ,[HomeOwnerFlag]
      ,[NumberCarsOwned]
  FROM [AdventureWorks2012].[Sales].[vPersonDemographics]
  order by BusinessEntityID
  option (fast 500)

Est vs Actual rows with option (fast 500)

Est vs Actual rows without option (fast 500)

A use case would be when an application is doing caching (load large amount of data in background) and wants to show the user a slice of data as quickly as possible.

Another interesting use case is in SSIS land that Rob Farley describes using FAST N as a catalyst speeding up the data retrieval.


By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster.

See Remus Rusanu's answer as well.

StackExchange Database Administrators Q#135455, answer score: 30