Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Creating an view with Option (Force order) Expand / Collapse
Posted Wednesday, September 18, 2002 4:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 18, 2005 11:40 AM
Points: 65, Visits: 1
Hello Sir

I have a query which has an option(FORCE ORDER)
select * from tab1 t1 inner join tab2 t2 on t1.c1 = t2.c1 inner join
tab3 t3 on t3.c1 = t2.c1

When i am executing this query alone its working fine..

But when i try to make it as a view as this select statement then its giving a syntax error AS
"Incorrect syntax near keyword OPTION"

Can u have a view which has a select statement with OPTION FORCE ORDER, if please tell me the syntax for that

Thnks in Advance

Post #6868
Posted Thursday, October 25, 2012 11:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:45 PM
Points: 1,385, Visits: 1,245
I stumbled across this abandoned post today while attempting to address the same issue, and thought I'd update it with my conclusion: It's simply not possible to specify the "OPTION (FORCE ORDER)" hint in a view definition (or, so that matter, any SELECT statement options), because the option can only ever be specified once per statement/query - it belongs in the final statement that actually uses the view.

I would love to find an alternative, because guaranteeing that this hint is always specified on queries that use a particularly complex view can be onerous, or even unrealistic, but for now that seems to be the only option. for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #1377171
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse