SQL Server Objects Created with SET Options
Category: Performance
What are SET options?
SQL Server backward compatibility SET options support ISO SQL standards for new development while allowing legacy T-SQL code to run unchanged on newer SQL Server versions.
In short, they prevent corruption of applications that expect non-ISO behavior.
Why should you care about this?
It is not recommended to create objects with these settings OFF when you want to add a filtered index, indexed views or other advanced features to the tables used inside the objects, these will cause problems.
How to find SQL Server objects with set options?
You can find objects with QUOTED_IDENTIFIER or ANSI_NULLS OFF using the following query.
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 the Varchar(n)/varbinary(n) columns with ANSI_PADDING OFF using the following query.
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 can we fix them?
1. To change the permanent OFF setting to ON, recreate the object from a session with QUOTED_IDENTIFER and ANSI_NULLS ON. Make sure the session setting is ON when executing DDL scripts.
2. To change the ANSI_PADDING setting for a persistent column from OFF to ON, run ALTER TABLE…ALTER COLUMN from an ANSI_PADDING ON session (specify the same definition as the current column).
