ScottPletcher (7/30/2014)

---

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.