I suggest that one always turn on both the QUOTED_IDENTIFIERS and ANSI_NULLS session settings. Not only do these settings provide ANSI-standard behavior, these must be turned on in order to use features like indexed views, indexes on computed columns and query notifications. It is tricky to ensure the settings are as desired, though, because the default session settings are different depending on the tools you use.
DDL Script Considerations
It is especially important to ensure the QUOTED_IDENTIFIERS and ANSI_NULLS session settings are correct with DDL scripts because both QUOTED_IDENTIFIERS and ANSI_NULL are "sticky". The settings in effect when a stored procedure, view, function or trigger are created are also used at execution time. The create time settings override run-time session settings.
SQLCMD and OSQL Turn Settings Off
QUOTED_IDENTIFIERS and ANSI_NULLS are on by default when you connect using modern client APIs like ODBC, SQLOLEDB, SQL Native Client and SqlClient. The SQL Server Management Studio and Query Analyzer tools keep those settings on unless you override the connection behavior under the tool connection options or run SET QUOTED_IDENTIFIERS ON or SET ANSI_NULLS ON commands in the query window.
The SQLCMD and OSQL command prompt utilities are different, tough. These tools explicitly turn off QUOTED_IDENTIFIERS after connecting, presumably to provide backwards compatibility. One must either specify the “-I” (upper-case “eye”) command-line argument to turn on QUOTED_IDENTIFIERS or include a SET QUOTED_IDENTIFIERS ON command in all the SQL scripts run from those utilities. I personally like avoid SET commands in my DDL scripts so I make it a habit to specify the -I command line option.
|re: QUOTED_IDENTIFIERS and ANSI_NULLS ON
I probably should have included a brief description of the settings for completeness even though the settings are detailed in the Books Online. Here's a synopsis.
The QUOTED_IDENTIFIERS setting controls how string literals and identifiers can be delimited. When QUOTED_IDENTIFIERS is ON, string literals must be enclosed in single quotes and identifiers can optionally be enclosed in double quotes or square brackets. With QUOTED_IDENTIFIERS OFF, string literals can be enclosed in either single or double quotes and identifiers can optionally enclosed be in square brackets only.
The ANSI_NULLS setting controls the result of NULL comparisons. With ANSI_NULLS ON, a comparison against a NULL value (e.g. WHERE MyColumn = NULL) results in UNKNOWN instead of true or false so one must use IS NULL to test for NULL values. When ANSI_NULLS is OFF, one can test for NULL using standard (in)equality comparison operators.
I should add that T-SQL code that is compatable with the ON settings will also work as expected when the settings are OFF. The reverse is not true.