Share via

SQL Server Create Database Defaults

Adam Newcombe 156 Reputation points
2020-11-26T12:11:32.413+00:00

A number of the default options are not desired

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF

But is it best to leave these , and individual connections override?

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

Answer accepted by question author

  1. Dan Guzman 9,516 Reputation points
    2020-11-26T12:55:32.233+00:00

    Although not desired, the default OFF settings exist due to an overabundance of backwards compatibility considerations. The default options are used only when the client API doesn't specify the settings when connecting.

    Modern client APIs (e.g. ODBC, OLE DB, SqlClient, JDBC, etc.) all turn those options ON when connecting, with the exception of ARITHABORT which is ON by default with a database compatibility level of 90 or higher (SQL 2005). The implication is changing the default database options will have no effect unless you are using an obsolete driver or unsupported SQL Server version (SQL 2008 or earlier).

    That said, there should be no harm in changing the defaults to the desired ON settings but I personally just ignore them.

    Was this answer helpful?

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.