Recommend/default settings for ANSI_NULLS and QUOTED_IDENTIFIER

sql-server, t-sql

Solution

`ANSI_NULLS` should always be `ON`. Always.

Here's why. From the documentation for `SET ANSI_NULLS ON`:

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

As for `QUOTED_IDENTIFIER`, that is more of an opinion-based question. I don't really care, because I never set the option explicitly, but if you do use it you should use it consistently everywhere. The reason I don't care is because I never quote identifiers with single or double quotes; I think this is very unreadable and makes them look like string:

SELECT 'alias' = "column" FROM "dbo"."table" AS 'alias';

Really? I much prefer using square brackets, and even then only when required (e.g. the identifier is a reserved word).

SELECT alias = [column] FROM dbo.[table] AS alias;

Problem

Original source