Optimize For in CTE or Subquery

Question

Setup:

I'm experimenting with the OPTIMIZE FOR clause in SQL Server 2008 R2. I've put the following at the end of my query.

OPTION (OPTIMIZE FOR (@UserType= 'M', @Date UNKNOWN))

Issue:

However, I'm getting the below error.

The variable "@UserType" is specified in the OPTIMIZE FOR clause, but is not used in the query.

This variable is used in a subquery within the query, and is only used in that location.

If I try moving the OPTIMIZE FOR into the subquery, the stored proc does not compile. Likewise, I cannot put the subquery in a CTE and optimize within that; that doesn't compile either.

Incorrect syntax near the keyword 'OPTION'.

Question:

Is there any way I can use an OPTIMIZE FOR clause within a CTE or subquery? I'd prefer to use a CTE since they are generally cleaner and more legible.

Answer 1

The option keyword must always be placed at the end of the outer most select query.

For example:

declare @var int = 1;

with cte
as (
    select @var as val
)
select * from cte
option (optimize for (@var = 2))