OPTION Clause in SQL Server

OPTION Clause in SQL Server

The OPTION clause is used to query the table with different query hint. This clause can be specified in different statements like SELECT, UPDATE, MERGE and DELETE. Only one OPTION clause can be specified in a statement. The syntax for OPTION clause is,

OPTION ( < query_hint > [ ,....n ] )

The different types of query_hint is as follows,

• FORCE ORDER
• LOOP JOIN
• MERGE JOIN
• HASH JOIN
• FORCE EXTERNALPUSHDOWN
• DISABLE EXTERNALPUSHDOWN

The query_hint specifies in which way the SQL server Database Engine processes the statement.

Using an OPTION clause with SELECT, UPDATE, MERGE and DELETE statement 

Lets see how to use OPTION clause in a SELECT statement.?

declare @q int;
set @q=1;
select * from tbl_EmployeeDetails
where Id>@q
order by Id
OPTION(OPTIMIZE FOR (@q UNKNOWN));

The OPTIMIZE FOR UNKNOWN is a optimizer hint. It is used when there is no parameter values have been passed to the query at all. Instructs query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization. The OPTIMIZE FOR UNKNOWN reuse the cache instead of recompiling each time which results in high performance.
The query returned result is,

Once we add the variable in the OPTION clause we must use that variable in our SELECT statement otherwise we will get an error like "The variable "@q" is specified in the OPTIMIZE FOR clause, but is not used in the query".

UPDATE Statement

The following statement demonstrates the usage of UPDATE query with OPTION clause.

declare @q int;
set @q=4;
update tbl_EmployeeDetails set Department='Sales'
where Id>@q
OPTION(OPTIMIZE FOR (@q UNKNOWN));

The above statement updates the employee department based on the @q scalar variable value. The statement returned result is,

MERGE Statement

The MERGE statement is used to combine multiple statements. The following query demonstrates the usage of MERGE statement with OPTION clause,

declare @q int;
set @q=4;
MERGE tbl_EmployeeDetails as e
USING tbl_BankDetails as b 
ON (e.Id=b.EmpID) 
and e.Id>@q
WHEN MATCHED THEN
update set e.Department='IT'
OPTION(OPTIMIZE FOR (@q UNKNOWN));
GO

The above statement updates the department details based on the matching of employee and its bank details. The statement returned result is,

DELETE Statement
The delete statement is simply straight forward to use it with OPTION clause.

declare @q int;
set @q=6;
delete from tbl_EmployeeDetails
where Id=@q
OPTION(OPTIMIZE FOR (@q UNKNOWN));
GO

The statement returned result is,

Using FAST N 
The FAST N is used to retrieve N-rows as soon as possible. A FAST N generates an execution plan with quickly returning the number of rows defined as N. The syntax for FAST N with OPTION clause is as follows,

OPTION ( FAST N );

Now, Lets see how to use with SELECT statement.?

select * from tbl_EmployeeDetails
order by Department
OPTION(FAST 5);

The statement returned result is,

This statement returns 5 rows as fast as possible. In order to get large number of rows this will be helpful to improve the performance.