sys.query_context_settings (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains information about the semantics affecting context settings associated with a query. There are a number of context settings available in SQL Server that influence the query semantics (defining the correct result of the query). The same query text compiled under different settings may produce different results (depending on the underlying data).

Column name Data type Description
context_settings_id bigint Primary key. This value is exposed in Showplan XML for queries.
set_options varbinary(8) Bit mask reflecting state of several SET options. For more information, see sys.dm_exec_plan_attributes (Transact-SQL).
language_id smallint The id of the language. For more information, see sys.syslanguages (Transact-SQL).
date_format smallint The date format. For more information, see SET DATEFORMAT (Transact-SQL).
date_first tinyint The date first value. For more information, see SET DATEFIRST (Transact-SQL).
status varbinary(2) Bitmask field that indicates type of query or context in which query was executed.
Column value can be combination of multiple flags (expressed in hexadecimal):

0x0 – regular query (no specific flags)

0x1 - query that was executed through one of the cursor APIs stored procedures

0x2 – query for notification

0x4 – internal query

0x8 – auto parameterized query without universal parameterization

0x10 – cursor fetch refresh query

0x20 - query that is being used in cursor update requests

0x40 - initial result set is returned when a cursor is opened (Cursor Auto Fetch)

0x80 – encrypted query

0x100 – query in context of row-level security predicate
required_cursor_options int Cursor options specified by the user such as the cursor type.
acceptable_cursor_options int Cursor options that SQL Server may implicitly convert to in order to support the execution of the statement.
merge_action_type smallint The type of trigger execution plan used as the result of a MERGE statement.

0 indicates a non-trigger plan, a trigger plan that does not execute as the result of a MERGE statement, or a trigger plan that executes as the result of a MERGE statement that only specifies a DELETE action.

1 indicates an INSERT trigger plan that runs as the result of a MERGE statement.

2 indicates an UPDATE trigger plan that runs as the result of a MERGE statement.

3 indicates a DELETE trigger plan that runs as the result of a MERGE statement containing a corresponding INSERT or UPDATE action.

For nested triggers run by cascading actions, this value is the action of the MERGE statement that caused the cascade.
default_schema_id int ID of the default schema, which is used to resolve names that are not fully qualified.
is_replication_specific bit Used for replication.
is_contained varbinary(1) 1 indicates a contained database.


Requires the VIEW DATABASE STATE permission.

See Also

sys.database_query_store_options (Transact-SQL)
sys.query_store_plan (Transact-SQL)
sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-SQL)
sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-SQL)
Monitoring Performance By Using the Query Store
Catalog Views (Transact-SQL)
Query Store Stored Procedures (Transact-SQL)
sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)