CODEWITHSUNDEEP
sql-server
Allan Xu
Allan Xu

Reputation: 9348

Database options: why by default "SET ANSI_NULLS OFF" is OFF for new databases?

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?

Upvotes: 11

Views: 4344

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 8

Related Questions