11

Using SSMS 2014:

When I create a new database using SSMS, the new database has some strange below default settings 

ALTER DATABASE [del] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [del] SET ANSI_NULLS OFF GO ALTER DATABASE [del] SET ANSI_PADDING OFF GO ALTER DATABASE [del] SET ANSI_WARNINGS OFF GO ALTER DATABASE [del] SET ARITHABORT OFF

When I test scripts and stored procedure, it seems that ANSI_NULLS is actually ON.

I am confused why such important setting like ANSI_NULLS is OFF when I create a database? and how it becomes ON in my scripts?

Improve this question

1 Answer 1

Reset to default
8

how it becomes ON in my scripts?

Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS.
By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to an instance of SQL Server.

I am confused why such important setting like ANSI_NULLS is OFF when I create a database?

I have created databases with both nulls off and on,but each time when i try to insert,Client drivers generated those set options as ON for me

Further below is what MSDN has to say about this..

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

closest,i can think of why this option is set to ON,may be due to compatabilty

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It is still strange and unclear why MS chose to have new databases created with ANSI NULLS OFF by default. Especially considering the text from MSDN that you quote, indicating that in the future they will let errors be raised even from explicitly setting ANSI_NULLS to False. I can't recall noticing such a choice while installing the server. You can however change this setting in the system database called "model", which is the model for all new databases created. The settings in model will act as a defaults for new databases.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.