0

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.

  • 1
    You can run DBCC USEROPTIONS to see the actual session settings. You'll need to run ISQL from an old SQL Server 2000 or earlier install to see the behavior with the ancient db-library API @TiborK mentioned in his answer. However, you won't be able connect with TLS 1.2 enabled. – Dan Guzman Sep 28 '19 at 17:56
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.