OPTION clause#
Specifies that the indicated query hint should be used throughout the entire query. Each query hint can be specified only one time, although multiple query hints are permitted. Only one OPTION clause can be specified with the statement.
This clause can be specified in the SELECT statement.
Syntax#
[ OPTION ( <query_hint> [ ,...n ] ) ]
Arguments#
query_hint
Keywords that indicate which optimizer hints are used to customize the way the Database Engine processes the statement. For more information, see Hints.
Examples#
Force the join order to match the order in the query#
The following example uses the FORCE ORDER hint to force the query plan to use the join order specified by the query. This will improve performance on some queries; not all queries.
-- Uses AdventureWorks
-- Obtain partition numbers, boundary values, boundary value types, and rows per boundary
-- for the partitions in the ProspectiveBuyer table of the ssawPDW database.
SELECT sp.partition_number, prv.value AS boundary_value, lower(sty.name) AS boundary_value_type, sp.rows
FROM sys.tables st JOIN sys.indexes si ON st.object_id = si.object_id AND si.index_id <2
JOIN sys.partitions sp ON sp.object_id = st.object_id AND sp.index_id = si.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = si.data_space_id
JOIN sys.partition_range_values prv ON prv.function_id = ps.function_id
JOIN sys.partition_parameters pp ON pp.function_id = ps.function_id
JOIN sys.types sty ON sty.user_type_id = pp.user_type_id AND prv.boundary_id = sp.partition_number
WHERE st.object_id = (SELECT object_id FROM sys.objects WHERE name = 'FactResellerSales')
ORDER BY sp.partition_number
OPTION ( FORCE ORDER )
;