Sean Lange (7/30/2014)
ScottPletcher (7/30/2014)
Keith Tate (7/30/2014)
What can happen if null is not defined?
If you don't set the NULL option then it will be nullable.
create table Test1 (ExpirationDate datetime);
go
That's not true. The default could be NULL or NOT NULL, and which one can be influenced by both database and/or session settings. Therefore, you should always explicitly specify "NULL" or "NOT NULL" on every column, since you won't know the database and/or session settings if/when the code is run again if the future.
I agree we should always define NULL or NOT NULL. What settings can change or influence this? I am not familiar with that.
From Books Online.
"
If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.
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.
When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULTis OFF), the default of NOT NULL is assigned.
"
Hmm, yeah, I'm not gonna try to guess the current state of all that, I'm just gonna code "NULL" or "NOT NULL" and be done with it :-D.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.