Useful SQL functions, syntax, and more - Part 19 (FAST?)

Recently I started working on SQL Server just a little bit on side job. Tuning is an area that I personally like very much because it's like putting the puzzle back together. While doing some research, I found a query hint that I'd never used called "FAST".

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

Is there anything this convenient for the keyword "fast search"? But I was interested in it, so I'll check out how it works as soon as possible. I'll use the following query.

SELECT 
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279;

Above execution plan as follows.

Next, let's add OPTION (FAST n). n = 100.

SELECT
    SOH.SalesOrderID,
    SOH.CustomerID,
    SOH.SalesPersonID
FROM 
    Sales.SalesOrderHeader SOH
WHERE 
    SalesPersonID = 279
OPTION (FAST 100);

　Above Execution plan as follows.

Let's compare the two plans. The number of rows is different and the cost is lower with OPTION (FAST 100) added.

The estimated number of rows is different. The one with OPTION (FAST 100) has 100. There is an attribute "EstimateRowsWithoutRowGoal = 429" that I've never seen before. (My knowledge of SQL Server stops at version 2012, but I don't remember this being around for a long time.)

The estimated number of rows has been reduced here as well, and "EstimateRowsWithoutRowGoal = 31465" has been added.

Hmmm, I haven't written an article in a while, but I'm halfway through. But I guess this is convenient because it's a good story to write an article about above, lol.

[ナイキ] エア ズーム テンポ Next% FK フライニット メンズ ランニング シューズ Air Zoom Tempo Next% FK CI9923-001, 27.0 cm [並行輸入品]