Sql-server – behavior change between SQL Server 2000 and SQL Server 2008 R2 OPTION (FAST 1)

hintssql serversql-server-2000sql-server-2008-r2

This is question 2 of 2 related to the FAST query hint. Background Information (as an aside, I'm not trying to garner more rep, I'm trying to be true to only asking one question per question).

Our legacy ERP was running on SQL Server 2000 EE and now it's on 2008 R2 EE. We're noticing many more blocked commands in the 2008R2 environment. The 2008R2 environment is running with trace flag 4199 and 1119.

I noticed a difference between the MSDN documentation on hints in 2000) vs. 2008R2 where the FAST hint is expanded to note that the result will continue to be processed once the specified n rows had been returned. Is this just a more robust document, is this a change in behavior for this hint through the years, or something different?

Best Answer

Yes this is just improved documentation.

FAST n has always been the n version of FASTFIRSTROW i.e. a request to the optimiser to choose a plan that will return n rows more quickly if a plan can be identified to do so.

It is not equivalent in anyway to requesting the TOP n rows, which is in effect what you would be asking for if the expectation was for query execution to terminate after n had been delivered.

Related Question