Availability: Hyper-Q 3.39.0 and later.
Hints are an addition to SQL that allow you to instruct Hyper-Q how to execute a given query. Hints are typically embedded in each statement that references the objects listed in the hints. Session hints let you specify hints at the session level, as opposed to passing them for each individual statement. Thus, all queries sent to the database using a given session will use the hints you provide. The hint will affect only those queries sent from the time the session hint is set. Any earlier queries in the same session are unaffected.
You specify session hints by including a scope in your hint comment. The possible scopes are
query. If no scope is specified, the default is to use query.
The session hint affects all queries starting from when the parameter is first specified, until either the end of the session, or until a new session value for the same parameter is specified at a later time within the session.
Note: Hints specified at the query level override the session-level hint for the duration of a particular query.
The syntax for hints is:
-- @dtm_param_name = value for session | query ;
||The name of the hint you want to use.|
||The value to assign to the hint.|
||The scope of the hint. You can specify either a session or a query level scope. If no scope is specified, the defaults is to use query.|
When specifying multiple parameters in a single statement, separate them with a semicolon.
Session Hint Examples
Enable Azure Synapse UPDATE Statements Within a Session
To improve performance, it can be useful to execute UPDATE statements using the native Azure Synapse SQL semantics instead of Hyper-Q’s emulation. You can enable Azure Synapse UPDATE statements using the
passthru_upd_with_join hint within a session.
-- @dtm_passthru_upd_with_join=on for session
Disable Transactions in a Session
The following example disables transactions in the session. Statements in the transaction will not be rolled back if an error occurs.
--@dtm_disable_user_transactions=true for session
delete from tbl_employee; insert into tbl_employees values(1,2,3)