SQL Server objects created with SET Options

Objects created with SET Options

What are the set options?

SQL Server backward compatibility SET options allow legacy T-SQL code to run on newer SQL Server versions without changes while supporting ISO SQL standards for new development.

Briefly, they avoid applications that expected non-ISO behavior from breaking.

By the way, this check is a part of our SQL Server Health Check service.

Why should you care about it?

Creating objects with these settings OFF is not recommended because if you ever want to add a filtered index, indexed views, or other advanced features to tables used within the objects, they cause issues (break).

How to find SQL Server objects with set options?

You can find objects with QUOTED_IDENTIFIER or ANSI_NULLS OFF using the query below.

  1. SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName
  2. ,OBJECT_NAME(o.object_id) AS ObjectName
  3. ,o.type_desc AS ObjectType
  4. FROM sys.objects AS o
  5. WHERE
  6. 0 IN( OBJECTPROPERTY(o.object_id, 'ExecIsQuotedIdentOn'), 
  7. OBJECTPROPERTY(o.object_id, 'ExecIsAnsiNullsOn'))

You can find Varchar(n)/varbinary(n) columns with ANSI_PADDING OFF using the query below.

  1. SELECT OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
  2. ,OBJECT_NAME(t.object_id) AS ObjectName
  3. ,c.name AS ColumnName
  4. FROM sys.tables AS t
  5. JOIN sys.columns AS c ON c.object_id = t.object_id
  6. JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
  7. WHERE c.is_ansi_padded = 0
  8. AND ((ty.name IN ('varbinary','varchar') AND c.max_length <> -1)
  9. OR (ty.name IN ('binary','char') AND c.is_nullable = 1))

How to fix them?

  1. Recreate the object from a session with QUOTED_IDENTIFER and ANSI_NULLS ON to change the persisted OFF setting to ON. Make sure the session setting is ON when executing DDL scripts.
  2. To change a persisted column ANSI_PADDING setting from OFF to ON, execute ALTER TABLE…ALTER COLUMN from an ANSI_PADDING ON session (specify the same definition as the existing column).

More information

Microsoft – SET ANSI_NULLS.

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *