Getting SQL Server Context Settings

Frank Gill has a script to find current Query Store context settings:

The following query will return the hex and integer value for each row in the table (NOTE: Query store must be enabled for the database to return values):

USE YourQueryStoreDatabase;
SELECT set_options, CONVERT(INT, set_options) AS IntSetOptions
FROM sys.query_context_settings;

The set_options value represents a bit mask, with each binary digit representing a specific set option. The full list of values can be found here. I created stored procedure ReturnSetOptions to take the IntSetOptions from the query above and return the set options represented. The code for the procedure is listed below.

Read on to get a script which breaks the bitmask field into human-readable results.

Related Posts

The Value Of Schemabinding

Vitaly Bruk explains what schemabinding is and why we sometimes need WITH SCHEMABINDING in our code: In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables […]

Read More

GROUP BY vs DISTINCT

Rob Farley looks at how GROUP BY and DISTINCT and lead you down different execution plan paths: What I want to explore in this post is the particular example that we both used… to bring an important point that could be missed because of the similarity of our examples. You see, we both happened to […]

Read More

Categories

November 2017
MTWTFSS
« Oct Dec »
 12345
6789101112
13141516171819
20212223242526
27282930