CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 375

SQL Server - OPTION CLAUSE

Can someone tell me if the OPTION clause is available in SQL Server 2012.

I cannot see any documentation to see when it was introduced.

I want to use OPTION (LOOP JOIN) on a SQL Server 2012 database...

Thanks

Upvotes: 3

Views: 8338

Answers (3)

Jose María Yohn
Jose María Yohn

Reputation: 11

Is available on all versions as long as I remember.

But as you need to state on what join you want to do the loop. You are setting incorrectly the hint:

select * from saleslt.customer c
inner join SalesLT.CustomerAddress ca
    on c.CustomerID = ca.CustomerID

The force nested loop should be:

select * from saleslt.customer c
inner LOOP join SalesLT.CustomerAddress ca
    on c.CustomerID = ca.CustomerID

You can also try to force MERGE and HASH joins. Anyway I encourage against those practices, and instead properly code the sqls so the optimizer is able to find out the right way to do the JOIN. An Increase on table data may set a Nested Loop as a bad execution plan, and you will be forcing it. Ensure the SQL, index design and statistics are ok, and you wont need those hints.

Cheers,

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453543

Can someone tell me if the OPTION clause is available in SQL Server 2012.

I cannot see any documentation to see when it was introduced.

Yes it is available in SQL Server 2012. The OPTION clause goes back to at least SQL Server 7.0

I want to use OPTION (LOOP JOIN) on a SQL Server 2012 database...

The availability of the OPTION clause does not mean that OPTION (LOOP JOIN) is available. These have been added to over various product versions.

Below is the BOL entry on hints for SQL Server 2000

enter link description here

In 2000 the only way of specifying join algorithms is at the individual join level (which also fixes the join order).

By 2005 BOL documents the following more extensive selection of query hints so OPTION (LOOP JOIN) is available from that version.

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] ) 
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
}

Following that these new hints were made available

  • SQL Server 2008 introduced OPTIMIZE FOR UNKNOWN and the TABLE HINT query hint
  • SQL Server 2012 introduced IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
  • SQL Server 2016 SP1
    • { FORCE | DISABLE } EXTERNALPUSHDOWN (Polybase)
    • MAX_GRANT_PERCENT = percent
    • MIN_GRANT_PERCENT = percent
    • NO_PERFORMANCE_SPOOL
    • USE HINT ( '<hint_name>' [ , ...n ] )
  • SQL Server 2019 { FORCE | DISABLE } SCALEOUTEXECUTION (BDC)

Also at some point QUERYTRACEON trace_flag was introduced - not sure when as I think it was available before being documented

Upvotes: 3

LaraRaraBoBara
LaraRaraBoBara

Reputation: 658

As Gordon Linoff stated, yes - the option (loop join) is available in SQL 2012.

I spun up a new SQL Database in Azure, changed the compatibility level to 110 (SQL 2012), and ran two queries. Screen shot of both execution plans shown as well.

select * from saleslt.customer c
inner join SalesLT.CustomerAddress ca
    on c.CustomerID = ca.CustomerID

select * from saleslt.customer c
inner join SalesLT.CustomerAddress ca
    on c.CustomerID = ca.CustomerID
OPTION (LOOP JOIN);

Loop Join Execution Plan

As stated by others, I also usually don't force hints but answering your question as stated: Yes, it is available.

Upvotes: 3

Related Questions