sys.query_context_settings (Transact-SQL)
Applies to: 
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
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. |
Permissions
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)