Troubleshooting Guide: SQL Server Error 1935 — Index Creation Failure Due to SET Options

Why This Error Happens

This error occurs because the object (table, view, or indexed view) was originally created with one or more required SET options disabled, such as QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, or NUMERIC_ROUNDABORT. SQL Server 2025 strictly enforces these settings when creating indexes—particularly indexed views, computed columns, or filtered indexes—because the query optimizer relies on deterministic session behavior to guarantee consistent results. If the connection or session that issued the original CREATE TABLE/CREATE VIEW statement had these options turned off (often due to legacy client drivers, ODBC/OLEDB connection defaults, or older application code), the metadata gets flagged, and any later CREATE INDEX operation on that object will fail until the options are corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic SET Options

Run the following query to inspect the current session settings before attempting the index creation:

SELECT 
    SESSIONPROPERTY('ANSI_NULLS') AS ANSI_NULLS,
    SESSIONPROPERTY('QUOTED_IDENTIFIER') AS QUOTED_IDENTIFIER,
    SESSIONPROPERTY('ANSI_PADDING') AS ANSI_PADDING,
    SESSIONPROPERTY('ANSI_WARNINGS') AS ANSI_WARNINGS,
    SESSIONPROPERTY('ARITHABORT') AS ARITHABORT,
    SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') AS CONCAT_NULL_YIELDS_NULL,
    SESSIONPROPERTY('NUMERIC_ROUNDABORT') AS NUMERIC_ROUNDABORT;

A value of 0 for any of these indicates the setting is OFF and needs correction.
Cross-reference the error message text ('%.*ls') which explicitly names the object and the specific SET option causing the conflict.

Step 2: Explicitly Set the Correct Session Options

Before running CREATE INDEX, force the correct settings in your current query window or script:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO

Re-run your CREATE INDEX statement immediately after this block, within the same session/batch.
If using SQL Server Management Studio (SSMS), these can also be set globally under Tools > Options > Query Execution > SQL Server > Advanced.

Step 3: Recreate the Underlying Object with Correct Settings

If the table or view itself was created with bad settings baked into its metadata, simply setting options at index-creation time won’t help—you must drop and recreate the object.
Script out the existing object definition (right-click table/view in SSMS > Script Table/View as > CREATE To).
Wrap the CREATE TABLE or CREATE VIEW statement with the correct SET options (as shown in Step 2) immediately preceding it in the same batch.
Execute the full script to rebuild the object with the proper session context now embedded in its metadata.

Step 4: Check Application/Driver Connection Defaults

If the object is created programmatically (via .NET, ODBC, JDBC, or legacy OLEDB drivers), verify the connection string or driver configuration isn’t overriding these defaults.
For ODBC connections on Windows Server 2025/Windows 11, open ODBC Data Source Administrator (odbcad32.exe), select the SQL Server DSN, and confirm “Use ANSI Quoted Identifiers” and “Use ANSI Null, Paddings and Warnings” checkboxes are enabled.
For application code (e.g., Entity Framework, legacy ADO), ensure SET QUOTED_IDENTIFIER ON is not being explicitly disabled in connection initialization scripts.

Alternative or Verification Steps

To verify the fix, run CREATE INDEX again on the affected object—if it completes without Error 1935, the SET options are now correctly aligned with SQL Server 2025 requirements.
As a quick diagnostic alternative, query sys.tables or sys.views with OBJECTPROPERTY(object_id, 'IsQuotedIdentOn') to confirm the object’s stored metadata reflects QUOTED_IDENTIFIER ON without needing to recreate it from scratch.