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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,998 questions
0 comments
Accepted answer
  1. Dan Guzman 9,236 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.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

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