SQL Server Objects Created with the Wrong SET Options: Msg 1934, Filtered Indexes and ARITHABORT

Some SET options in SQL Server don’t just affect the current session; they permanently affect the objects you create. A stored procedure, view or trigger records the ANSI_NULLS and QUOTED_IDENTIFIER settings in effect when it was created, and every time it runs it uses those recorded settings, whatever the calling session’s settings are. A procedure created years ago with the wrong settings can start failing the moment a filtered index is added to a table today.

This article covers which SET options matter, which features require the right settings, how to find problem objects, and how these settings relate to the “fast in SSMS, slow in the application” problem.

Which Features Require the Right SET Options?

Filtered indexes, indexed views, indexes on computed columns, XML data type methods and a few other features require the session working on the tables that use them to run with these settings:

If that isn’t met, for example when a procedure that inserts into a table with a filtered index was created with QUOTED_IDENTIFIER OFF, the operation fails with this error:

This error usually comes from an old procedure that writes to a table, right after a new filtered index is added to it for performance.

Why Are ANSI_NULLS and QUOTED_IDENTIFIER Permanent?

Other SET options come from the session at execution time, but these two are recorded when the module is created. So if you created a procedure with a script containing SET QUOTED_IDENTIFIER OFF, the procedure always runs with OFF. This is typically seen in objects created with old tools or with script templates that explicitly turn these settings off.

Finding Problem Objects

SELECT OBJECT_SCHEMA_NAME(m.object_id) + N'.' + OBJECT_NAME(m.object_id) AS object_name,
       o.type_desc,
       m.uses_ansi_nulls,
       m.uses_quoted_identifier
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.uses_ansi_nulls = 0 OR m.uses_quoted_identifier = 0
ORDER BY o.type_desc, object_name;

Table columns also keep the ANSI_PADDING setting from when they were created; older tables may contain char and varchar columns created with OFF:

SELECT OBJECT_NAME(c.object_id) AS table_name, c.name AS column_name,
       TYPE_NAME(c.user_type_id) AS data_type, c.is_ansi_padded
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE c.is_ansi_padded = 0
  AND TYPE_NAME(c.user_type_id) IN (N'char', N'varchar', N'binary', N'varbinary');

Fixing It

The way to fix a problem module is to recreate it with the right settings:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.AddOrder
    @CustomerId int, @Amount decimal(19, 4)
AS
BEGIN
    INSERT INTO dbo.Orders (CustomerId, Amount) VALUES (@CustomerId, @Amount);
END
GO

With QUOTED_IDENTIFIER ON, double quotes delimit object names instead of strings. If old code writes strings in double quotes (WHERE Status = "Active"), change them to single quotes before recreating; otherwise you get an “Invalid column name” error.

“Fast in SSMS, Slow in the Application”: ARITHABORT

SSMS connects with ARITHABORT ON by default, while many application drivers connect with OFF. Because SET options are part of the plan cache key, the same query run from SSMS and from the application compiles two separate plans. If the application’s plan was compiled for an unlucky parameter value, the query looks slow in the application and fast in SSMS; that usually comes from the different plans, not from ARITHABORT itself.

Checklist

• All modules were created with ANSI_NULLS and QUOTED_IDENTIFIER ON.
• Script templates don’t turn these settings off.
• Objects that write to a table were checked before adding a filtered index or indexed view.
• Strings written in double quotes were changed to single quotes.
• Plan differences between the application and SSMS were compared through set_options.