Sql-server – SQL Server : reasons not to use default database settings for ANSI_NULLS

sql-server-2008-r2

I've been given a case where a customer is experiencing this error – once in a while:

Msg 8624, Level 16, State 21, Line 1
Internal Query Processor Error:
The query processor could not produce a query plan. For more
information, contact Customer Support Services.

They're using our software, and running it on SQL Server 2008 R2 (RTM) and at compatibilty level 100 (SQL Server 2008). The database however was originally created on a 2000 or 2005 machine (can't reproduce anymore) and then moved to 2008 R2 recently.

The stored procedure in question has an ugly looking INSERT statement which grabs data from ten (yes!) "copies" of a given table, all joined with RIGHT OUTER JOIN against one another (same table – ten JOINs against it).

The one solution I found that seems most appropriate has to do with various settings – it's recommended to use:

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set ARITHABORT ON

set NUMERIC_ROUNDABORT OFF

OK – sure – I can set these before every stored procedure I create (or alter).

My question to the database gurus out there would be: any risk when I set these as default settings for my database?

E.g.

ALTER DATABASE MyDB SET ANSI_NULLS ON

and be done with it? Thoughts? Insights? Recommendations?

Best Answer

It is not a risk at all. In fact, the ability to turn ANSI_NULLS OFF has a limited lifespan. Soon enough that won't even be an option, it'll always be ON. The only time I've ever seen any form of reasoning for setting ANSI_NULLS OFF, is because there are developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reasoning and not justifiable. I look forward to the permanent ON setting for ANSI_NULLS.

Related Question