SQL Server Tips

Managing SQL Server Objects with SET Options

Updated October 29, 20253 min read

SQL Server objects and SET options. A mixing desk has eight sliders, each set to a different height.

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 prevent applications that expect non-ISO behavior from breaking.

Why should you care about it?

Creating objects with these settings set to OFF is not advised. Should you later wish to add filtered indexes, indexed views, or other advanced features to tables used within these 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.

T-SQL

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName
	,OBJECT_NAME(o.object_id) AS ObjectName
	,o.type_desc AS ObjectType
FROM sys.objects AS o
WHERE 0 IN (
		OBJECTPROPERTY(o.object_id, 'ExecIsQuotedIdentOn')
		,OBJECTPROPERTY(o.object_id, 'ExecIsAnsiNullsOn')
		)

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

T-SQL

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
	,OBJECT_NAME(t.object_id) AS ObjectName
	,c.name AS ColumnName
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id
	AND ty.user_type_id = c.user_type_id
WHERE c.is_ansi_padded = 0
	AND (
		(
			ty.name IN (
				'varbinary'
				,'varchar'
				)
			AND c.max_length <> - 1
			)
		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 persistent 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

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜