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.