ANSI NULL OFF By Default in database option 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 OFF since 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.
Some of the database settings are useless. As you have noticed.

The client API (ODBC etc) will override them. And, the client app (SSMS for instance) can further override whatever was overridden by the API.

So why are those database settings still there? Because stone-age API didn't do such override. I'm thinking DbLibrary here, but this might apply to other very old APIs as well.

Why do they have such weird database option values as default? Because of backwards compatibility. I.e., so you can create a database "as default" and use a stone-age API "as default" and get the same behaviour as you did back in 1989.