OPTION (MAXDOP) with sub query?

Question

SELECT [SomeColumn]
FROM [Table1] t1 
WHERE Table1ID = (
        SELECT [Table1ID]
        FROM [Table2] t2 
        WHERE t2.ID = 2
        OPTION (MAXDOP 4)
        )
OPTION (MAXDOP 4)

This gives an error Incorrect syntax near the keyword 'OPTION'. on the first OPTION (MAXDOP) Is it possible to use OPTION (MAXDOP) in a sub query? Or the final OPTION (MAXDOP) will be good enough to set the degree for the sub query as well.

Answer 1

The subquery might not be in one piece at runtime. The optimizer can pick it apart and reorder everything. This query will certainly not execute as written. Therefore a maxdop for a subquery does not apply to SQL Servers execution model.

There can only be one maxdop for the whole query. There is a logical reason for this restriction.