Here's the relevant part from MS SQL docs about what the default for NULL / NOT NULL is if it is not specified:
When column nullability is not explicitly specified, column nullability follows the rules shown in the following table.
NULLABILITY RULES WITHIN A TABLE DEFINITION|Column data type|Rule|
| --- | --- |
|Alias data type|The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use **sp_help** .|
|CLR user-defined type|Nullability is determined according to the column definition.|
|System-supplied data type|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.
To view the database setting for ANSI_NULL_DEFAULT, use the **sys.databases** catalog view|
When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULT is OFF), the default of NOT NULL is assigned.
Uh, yeah, right, I think I'll just specify it myself instead!