SQLTeam.com | Weblogs | Forums

Altering column in a table


I am having a table Obsevations & columns ObsevationsId,ObsevationsName, ObsevationsDate.

ObsevationsDate is having datatype date not null

I am executing

ALTER TABLE Obsevations 
ALTER COLUMN ObsevationsDate datetime;

Will this command make this column ObsevationsDate nullable? Why?


Have you tried it?

1 Like

The default for NULL / NOT NULL is so complex that it's basically impossible to tell (without tons of research, which is wasted time really).

The best rule is to ALWAYS specify NULL or NOT NULL when ALTERing a column on a table.

If you don't specify it, just because you get one result today, say NULL, does NOT mean you will get that same result tomorrow. Don't risk it!

1 Like

Sounds like an interview question and so I have to ask the same question that @yosiasz did . You should try it several different ways including redefining the column as the original datatype and see what happens, then make a speculation, and then search for why.

Then, take @ScottPletcher 's advice seriously to heart.

1 Like

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.

| --- | --- |
|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!


Amen to that!

Best practice