But I needed to verify what options were in place on a query so that I could make sure that SSMS matched, since I'd changed settings over the years (and new SSMS, who dis?). I asked the friendly neighborhood LLM and it flat out made up options and flags, but at least it gave me a basic idea how to do the query.
So run this query first in the query store to get your set_options:
SELECT q.query_id, q.query_text_id, q.object_id, OBJECT_NAME(q.object_id) AS [object_name], q.context_settings_id, cs.set_options, -- Binary representation of SET options q.query_parameterization_type_desc FROM sys.query_store_query q JOIN sys.query_context_settings cs ON q.context_settings_id = cs.context_settings_id WHERE q.object_id = OBJECT_ID('yourSPhere'); SELECT q.query_id, convert(bigint, cs.set_options) as set_options, cs.language_id, cs.date_format, cs.date_first, cs.default_schema_id FROM sys.query_store_query q JOIN sys.query_context_settings cs ON q.context_settings_id = cs.context_settings_id WHERE q.object_id = object_id('dbo.yourSPhere')
and then this to get your final answer:
DECLARE @set_options_value INT = 4347; -- Replace with your observed value PRINT 'Set options for value ' + CAST(@set_options_value AS VARCHAR) + ':'; IF @set_options_value & 1 = 1 PRINT ' - ANSI_PADDING'; IF @set_options_value & 2 = 2 PRINT ' - Parallel Plans'; IF @set_options_value & 4 = 4 PRINT ' - FORCEPLAN'; IF @set_options_value & 8 = 8 PRINT ' - CONCAT_NULL_YIELDS_NULL'; IF @set_options_value & 16 = 16 PRINT ' - ANSI_WARNINGS'; IF @set_options_value & 32 = 32 PRINT ' - ANSI_NULLS'; IF @set_options_value & 64 = 64 PRINT ' - QUOTED_IDENTIFIER'; IF @set_options_value & 128 = 128 PRINT ' - ANSI_NULL_DFLT_ON' IF @set_options_value & 256 = 256 PRINT ' - ANSI_NULL_DFLT_OFF' IF @set_options_value & 512 = 512 PRINT ' - NoBrowseTable' IF @set_options_value & 1024 = 1024 PRINT ' - TriggerOneRow' IF @set_options_value & 2048 = 2048 PRINT ' - ResyncQuery' IF @set_options_value & 4096 = 4096 PRINT ' - ARITH_ABORT' IF @set_options_value & 8192 = 8192 PRINT ' - NUMERIC_ROUNDABORT' IF @set_options_value & 16384 = 16384 PRINT ' - DATEFIRST' IF @set_options_value & 32768 = 32768 PRINT ' - DATEFORMAT' IF @set_options_value & 65536 = 65536 PRINT ' - LanguageID' IF @set_options_value & 131072 = 131072 PRINT ' - UPON';
