Menu
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?
397
ANSI_NULLS should be set to ON for executing distributed queries. ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure. When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed. When SET ANSI_DEFAULTS is ON, QUOTED_IDENTIFIER is also ON.
SET ANSI_PADDING. The ANSI_PADDING setting controls how trailing spaces are handled in columns with CHAR and VARCHAR data types, and trailing zeroes in columns with BINARY and VARBINARY data types. In other words, it specifies how the column stores the values shorter than the column defined size for that data types.
ANSI_NULLS define the comparison rule for NULL values in SQL Server. When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. Example.
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
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
