How can I make T-SQL perform OPTION (FORCE ORDER) when defining views?

Question

My DBA tells me that I should always use OPTION (FORCE ORDER) in my SQL statements when accessing a particular set of views. I understand this is to prevent the server vetoing the order of his joins.

Fair enough - it's worth while keeping the DBA happy and I am happy to comply.

However, I would like to write a couple of views in my own schema, but this isn't supported apparently.

How then, can I achieve the same when writing my views, ie having OPTION (FORCE ORDER) being enforced?

Thanks Fred

Answer 1

Blindly appending OPTION (FORCE ORDER) onto all queries that reference a particular view is extremely poor blanket advice.

OPTION (FORCE ORDER) is a query hint and these are not valid inside a view - you would need to put it on the outer level on all queries referencing your own views.

It is valid to use Join hints inside views though and

If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords.

So

SELECT v1.Foo,
       v2.Bar
FROM   v1
       INNER HASH JOIN v2
         ON v1.x = v2.x;

Would enforce the join order inside v1 and v2 (as well as enforcing the join ordering and algorithm between them).

But I would not recommend this. These types of hints should only be used in an extremely targeted manner in a last resort after not being able to get a satisfactory plan any other way. Not as a matter of policy without even testing alternatives.