November 25, 2019 at 12:00 am
Comments posted to this topic are about the item The Default NULL
November 25, 2019 at 6:29 am
Interesting question, thanks Steve.
IMHO, it is much more reliable to specifically set the column's nullability during create / Alter table
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 25, 2019 at 8:54 am
Not to nit-pick, but the option is actually called ANSI_NULL_DFLT_OFF, so technically, all the answers are incorrect.
However, the database option is called ANSI_NULL_DEFAULT, which can be set to OFF or ON. I would definitely recommend having this set to OFF, so no columns are created nullable unless explicitly specified otherwise.
November 25, 2019 at 9:09 am
Not to nit-pick, but the option is actually called ANSI_NULL_DFLT_OFF, so technically, all the answers are incorrect.
However, the database option is called ANSI_NULL_DEFAULT, which can be set to OFF or ON. I would definitely recommend having this set to OFF, so no columns are created nullable unless explicitly specified otherwise.
That's not what the documentation says
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql
When any session settings are set ON by using SET:
ANSI_NULL_DFLT_ON = ON, NULL is assigned.
ANSI_NULL_DFLT_OFF = ON, NOT NULL is assigned.
When any database settings are configured by using ALTER DATABASE:
ANSI_NULL_DEFAULT_ON = ON, NULL is assigned.
ANSI_NULL_DEFAULT_OFF = ON, NOT NULL is assigned.
November 25, 2019 at 9:34 am
Well, unfortunately, the documentation is a bit off. The bit about the session settings is correct, which matches what I wrote.
The bit about the database settings is incorrect. The correct information can be seen in https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15:
<sql_option> ::= { ANSI_NULL_DEFAULT { ON | OFF } | ANSI_NULLS { ON | OFF } | ANSI_PADDING { ON | OFF } | ANSI_WARNINGS { ON | OFF } | ARITHABORT { ON | OFF } | COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 } | CONCAT_NULL_YIELDS_NULL { ON | OFF } | NUMERIC_ROUNDABORT { ON | OFF } | QUOTED_IDENTIFIER { ON | OFF } | RECURSIVE_TRIGGERS { ON | OFF } }
ANSI_NULL_DEFAULT { ON | OFF } Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability isn't explicitly defined in CREATE TABLE or ALTER TABLE statements. Columns that are defined with constraints follow constraint rules whatever this setting may be.
ON
The default value for an undefined column is NULL.
OFF
The default value for an undefined column is NOT NULL.
Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session, by default. The clients run the statement when you connect to an instance of SQL Server. For more information, see SET ANSI_NULL_DFLT_ON.
For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.
Clearly, Microsoft has caused themselves a bit of confusion by naming the options the way they've done, as they can obviously not keep the documentation in sync with itself.
November 25, 2019 at 3:30 pm
What Rune Bivrin said.
November 25, 2019 at 5:36 pm
Updated the answers with the correct spelling.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply