Availability: Hyper-Q 3.39.0 and later.
dtm_add_synapse_query_options hint lets you add options to Transact-SQL queries for Azure Synapse. You can use
dtm_add_synapse_query_options in the
dtm.ini configuration file or as a DTM command. Typically, you use
dtm_add_synapse_query_options on a per request basis.
Query hints such as FORCE ORDER and USE HINT are supplied to Azure Synapse as an OPTION clause, which specifies that the indicated query hint be used throughout the entire query.
You can use the OPTION clause in SELECT, DELETE, UPDATE and MERGE statements. To learn more, see the Microsoft article OPTION Clause (Transact-SQL).
The syntax for
-- @dtm_add_synapse_query_options = options
||Hint that lets you add the OPTION clause to the resulting Azure Synapse statement.|
||The options to assign to the query.|
Specify an Option for Azure Synapse Analytics
This example instructs Azure Synapse to use the FORCE ORDER hint to join the tables in the exact order specified in the SQL statement. It also specifies that the query use the legacy cardinality estimator query hint (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')).
-- @dtm_add_synapse_query_options=FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')
FROM tblCustomer WHERE i > 1
Specify a Hint for the Duration of a Session
for session hint, you can specify that a hint be used for the duration of a session, as opposed to for a single query.
-- @dtm_add_synapse_query_options = FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')