What is the recommended configuration for ANSI settings?

Question

I saw a lot of questions regarding ANSI settings, and read some documents that indicate some functions (for example, indexes on computed columns and indexed views) may become useless depending on ANSI settings on or off ... So, what are the recommended values for them:

• ANSI_Padding
• ANSI_NULLS
• ANSI_WARNINGS
• Concat_NULL_YELDS_NULL
• QUOTED_IDENTIFIER
• ARITHABORT
• NUMERIC_ROUNDABORT

I would like recommendations to address these.

Answer 1

For indexed views and indexed or stored computed columns, all SET OPTIONS are specified

 SET options Required value --------------------- ------------- ANSI_NULLS ON ANSI_PADDING ON ANSI_WARNINGS* ON ARITHABORT ON CONCAT_NULL_YIELDS_NULL ON NUMERIC_ROUNDABORT OFF QUOTED_IDENTIFIER ON 

The SQL Server 2005 parameter from ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON unless the database compatibility level is set to 80 or earlier (if explicitly specified).

Regardless, it makes sense to be consistent in setting up ARITHABORT , as it is used as a key in the plan cache, and inconsistency can lead to duplication of plans that lose valuable plan cache space. You can see this from the request below.

 SET ARITHABORT OFF GO SELECT * FROM master..spt_values WHERE number= -10 /*plan_cache_test*/ GO SET ARITHABORT ON GO SELECT * FROM master..spt_values WHERE number= -10 /*plan_cache_test*/ GO SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE attribute = 'set_options' AND text LIKE '%plan_cache_test%' AND text NOT LIKE '%this_query%' 

Even without indexed view / constant column requirements, the OFF parameters are outdated for the following ANSI_PADDING , ANSI_NULLS , CONCAT_NULL_YIELDS_NULL , and for XQuery and XML statements, data modification requires QUOTED_IDENTIFIER be ON .