SQL OPTION

What is the SQL OPTION clause?

Applies one or more query-level hints that override the SQL Server optimizer for a single statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OPTION: Supported: Microsoft SQL Server (2005+), Azure SQL Database, Azure Synapse Analytics, SQL Server on Linux, Amazon RDS SQL Server, Sybase ASE (similar syntax) Not supported: PostgreSQL, MySQL, MariaDB, Oracle, SQLite, Snowflake

SQL OPTION Full Explanation

The OPTION clause in Microsoft SQL Server (and compatible platforms such as Azure SQL Database and Synapse Analytics) lets you attach query hints directly to an individual SELECT, INSERT, UPDATE, DELETE, or MERGE statement. These hints influence how the query processor chooses execution plans without changing database-wide settings. Common hints include RECOMPILE (do not reuse the cached plan), MAXDOP n (limit degree of parallelism), FAST n (return the first n rows as quickly as possible), OPTIMIZE FOR (explicit parameters), and LOOP JOIN / HASH JOIN (force specific join strategies).OPTION hints are applied after the main statement text and before a terminating semicolon. They affect only the statement they are attached to, leaving other queries and sessions unaffected. If multiple hints conflict, SQL Server returns an error. Some hints, such as RECOMPILE, may increase CPU cost; others, like MAXDOP, can improve concurrency. Always test performance and include OPTION hints only when the default optimizer choices are sub-optimal.The clause is unique to the SQL Server family; other databases either lack it or provide different mechanisms (MySQL optimizer hints, Oracle /*+ */ hints, PostgreSQL GUCs).

SQL OPTION Syntax

-- SELECT
SELECT col1, col2
FROM dbo.Facts WITH (INDEX = IX_Facts_Date)
WHERE event_date BETWEEN @Start AND @End
OPTION (RECOMPILE, MAXDOP 4);

-- UPDATE
UPDATE dbo.Users
SET last_login = SYSDATETIME()
WHERE id = @UserId
OPTION (HASH JOIN);

-- MERGE
MERGE dbo.Target AS t
USING dbo.Source AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val)
OPTION (OPTIMIZE FOR (@BatchSize = 500));

SQL OPTION Parameters

query_hint STRING One or more optimizer hints separated by commas. Supported hints include - RECOMPILE, MAXDOP n, FAST n, HASH JOIN, LOOP JOIN, MERGE JOIN, OPTIMIZE FOR (parameter=value), USE PLAN 'xml_plan', QUERYTRACEON n, etc.

Example Queries Using SQL OPTION

-- Force recompile each time
SELECT *
FROM dbo.Orders
WHERE customer_id = @CustId
OPTION (RECOMPILE);

-- Limit parallelism
SELECT SUM(amount) AS total
FROM dbo.Payments
OPTION (MAXDOP 1);

-- Return first 10 rows quickly
SELECT *
FROM dbo.Events
ORDER BY created_at DESC
OPTION (FAST 10);

-- Fix cardinality by hinting a value
DECLARE @days INT = 30;
SELECT *
FROM dbo.Sessions
WHERE datediff(day, start_time, end_time) > @days
OPTION (OPTIMIZE FOR (@days = 7));

Expected Output Using SQL OPTION

Each statement executes normally but SQL Server honors the specified hints
For example, RECOMPILE generates a fresh plan, MAXDOP 1 disables parallelism, FAST 10 prioritizes the first ten rows, and OPTIMIZE FOR uses 7 as the parameter sniffing value

Use Cases with SQL OPTION

Stabilizing a critical report that suffers from parameter sniffing issues
Reducing CPU by limiting parallel workers in a high-concurrency OLTP system
Forcing a loop join when the optimizer incorrectly chooses a hash join
Quickly returning a small preview of a large result set
Testing alternative plans during performance tuning

Common Mistakes with SQL OPTION

Assuming OPTION hints apply to every statement in a batch (they only apply to the statement they follow)
Combining mutually exclusive hints such as HASH JOIN and LOOP JOIN, which triggers error 8622
Forgetting the semicolon before another statement, causing a syntax error
Overusing RECOMPILE, leading to excessive CPU due to constant plan generation
Expecting OPTION to work in non-SQL Server databases like PostgreSQL or MySQL

First Introduced In

SQL Server 2005

Frequently Asked Questions

Does OPTION affect the entire batch?

Only the single statement that immediately precedes the OPTION clause is affected. Other statements in the batch run with normal optimizer behavior.

How do I combine multiple hints?

Place them inside one pair of parentheses and separate with commas, for example OPTION (HASH JOIN, MAXDOP 4).

What happens if hints conflict?

SQL Server raises error 8622 and stops execution. Remove or change the conflicting hints.

Is OPTION safe to use in production?

Yes, when applied judiciously and after testing. Avoid blanket usage; focus on problem queries where the default plan is sub-optimal.