Optimizing for something else

Ah, the feeling when you think of a new blog post topic, only to realize that you already wrote a post about that six years ago.

Well, I’ve done the work, so I might as well have a little fun with it.

My original idea was to build a simple T-SQL query that presents the caller with a list of connection flags and their current settings. Turns out, I already wrote one of those. So if you want to skip my antics and go directly to that post, I’m not going to blame you.

Let’s create a challenge

I want to create a T-SQL query that presents the following resultset, based on the current connection flags, on no more than two rows:

SET DISABLE_DEF_CNST_CHK, IMPLICIT_TRANSACTIONS, CURSOR_CLOSE_ON_COMMIT, ARITHIGNORE, NOCOUNT, ANSI_NULL_DFLT_OFF, NUMERIC_ROUNDABORT, XACT_ABORT OFF;

SET ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULL_DFLT_ON, CONCAT_NULL_YIELDS_NULL ON;

The challenge is to make the T-SQL code as brief as possible. However, in the interest of readability, I’m not going to count whitespaces or column names.

Prerequisites

The @@OPTION parameter holds a bitmap of all connection flags (15 of them, from 0 to 14), so to find out if a specific connection flag is set, you can perform a bitwise and on that flag’s bit and @@OPTIONS.

For instance, to check if ANSI_PADDING, i.e. bit number 4, is currently enabled, you would

SELECT POWER(2, 4) & @@OPTIONS;

If the result of the comparison is 0, the flag is not enabled. If the result is in fact 2⁴, the flag is set for the current connection.

The STRING_AGG solution

Here’s a solution that will work for SQL Server 2017 and newer:

WITH bits AS (
    SELECT i, flag, (CASE POWER(2, i) & @@OPTIONS WHEN 0 THEN 'OFF' ELSE 'ON' END) AS [state]
    FROM (
        VALUES ( 0, 'DISABLE_DEF_CNST_CHK'),    ( 1, 'IMPLICIT_TRANSACTIONS'),
               ( 2, 'CURSOR_CLOSE_ON_COMMIT'),  ( 3, 'ANSI_WARNINGS'),
               ( 4, 'ANSI_PADDING'),            ( 5, 'ANSI_NULLS'),
               ( 6, 'ARITHABORT'),              ( 7, 'ARITHIGNORE'),
               ( 8, 'QUOTED_IDENTIFIER'),       ( 9, 'NOCOUNT'),
               (10, 'ANSI_NULL_DFLT_ON'),       (11, 'ANSI_NULL_DFLT_OFF'),
               (12, 'CONCAT_NULL_YIELDS_NULL'), (13, 'NUMERIC_ROUNDABORT'),
               (14, 'XACT_ABORT')
        ) AS n(i, flag)
    )

SELECT 'SET '+STRING_AGG(flag, ', ') WITHIN GROUP (ORDER BY i)+' '+[state]+';' AS flags
FROM bits
GROUP BY [state];

Reading from top to bottom, the “bits” common table expression creates a recordset with one row for each flag. Every row is compared to @@OPTIONS to create a column called “state”, which can either be “ON” or “OFF”.

Finally, we’ll just aggregate the results with STRING_AGG, grouped by the “state” column.

In my opinion, this is a simple, reasonably easy-to-understand solution.

The legacy equivalent

“But Daniel, I’m on SQL Server 2008”, I hear you say.

Don’t worry, there’s a solution that will run on your old server, too. It relies on the good old XML-based string aggregation method. You’ll recognize the basics, like the CTE with the options, but the aggregation happens by creating one XML recordset for each “state”. That recordset is converted to a serialized string, which is then used to display the results to the user.

WITH bits AS (
    SELECT flag, (CASE POWER(2, i) & @@OPTIONS WHEN 0 THEN 'OFF' ELSE 'ON' END) AS [state]
    FROM (
        VALUES ( 0, 'DISABLE_DEF_CNST_CHK'),    ( 1, 'IMPLICIT_TRANSACTIONS'),
               ( 2, 'CURSOR_CLOSE_ON_COMMIT'),  ( 3, 'ANSI_WARNINGS'),
               ( 4, 'ANSI_PADDING'),            ( 5, 'ANSI_NULLS'),
               ( 6, 'ARITHABORT'),              ( 7, 'ARITHIGNORE'),
               ( 8, 'QUOTED_IDENTIFIER'),       ( 9, 'NOCOUNT'),
               (10, 'ANSI_NULL_DFLT_ON'),       (11, 'ANSI_NULL_DFLT_OFF'),
               (12, 'CONCAT_NULL_YIELDS_NULL'), (13, 'NUMERIC_ROUNDABORT'),
               (14, 'XACT_ABORT')
        ) AS n(i, flag)
    )

SELECT 'SET '+SUBSTRING(CAST((
    SELECT ', '+bits.flag FROM bits WHERE [state]=x.[state] FOR XML PATH('')
    ) AS varchar(1000)), 3, 1000)+' '+x.[state]+';' AS flags
FROM (
    SELECT DISTINCT [state] FROM bits
    ) AS x([state]);

Conclusion

Ok, I had already written the T-SQL, and I wasn’t exactly going to throw it away. Think you can do better? Go ahead and give it a shot in the comments!