Today we are going to discuss on SQL Server query hints execution plan. Let us consider following SQL statement;
USE [AdventureWorks2012] SELECT * FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetail.ProductID
Imagine that our application is only concerned about First N rows at a time and similar execution plan shown in above figure can be an expensive one though it is chosen to be most appropriate to rerun records by SQL Server optimizer. We can include FAST hint to get these records quicker. After applying FAST hint in the statement, the new plan includes Index Scan and Key Lookup (read here Part1, Part2, Part3, Part4) on the clustered table and help to return FIRST N rows quickly and is shown in the following example.
USE [AdventureWorks2012] SELECT * FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetail.ProductID OPTION (FAST 10)
This hint is used to optimize a query to retrieve First N rows of records as quickly as possible and is helpful in situations where only first few rows are relevant for the query result. This is not applicable for the remaining rows that are returned by the query.
The downside is, remaining records may take longer time because the query is optimized for first N records and not for all. In the example, Sort operator (links) is efficient way to get the records sorted ProductID but this is a blocking operator i.e. SQL Server will not produce any records until Sort operation is completed.