Use a Hyper-Q Hint for an Entire Session


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 session and 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 ;


Parameter Description
param_value The name of the hint you want to use.
value The value to assign to the hint.
for session | query 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)
Have more questions? Submit a request


Please sign in to leave a comment.