I have a weird behaviour here when creating database in sql server. The default ANSI values is set to false when creating database.
ALTER DATABASE [NewDB] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [NewDB] SET ANSI_NULLS OFF GO ALTER DATABASE [NewDB] SET ANSI_PADDING OFF GO ALTER DATABASE [NewDB] SET ANSI_WARNINGS OFF GO ALTER DATABASE [NewDB] SET ARITHABORT OFF GO
However, when i execute the query after database is created in query editor
SELECT * FROM [dbo].[TableName] WHERE [ColumnName] = NULL
It is obvious that the actual setting is
ANSI_NULLS ON since no row returns.
My question is:
1) Why is this so? The database option is
ANSI_NULLS OFF but query result actually show it is
ANSI_NULLS ON. This is also applicable to all other ANSI settings, where it actually resembles
ANSI_NULLS ON while the database option is set to OFF.
2) Why is the default database option is
ANSI_NULLS OFF instead of
ANSI_NULLS ON. (Similar applicable to other ANSI options as well, why is it default to
ON is the one being recommended?)
I have tested this from SQL Server 2012 up to 2017 and all version results in same behaviour. I am quite confused with this.