Odd Behavior With Altering Columns

Solomon Rutzky points out a few things which you can unintentionally change when running an ALTER TABLE [tbl] ALTER COLUMN [col] command:

If the column is NOT NULL, then not specifying NOT NULL will cause it to become NULLable. The documentation for ALTER TABLE even states:

ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.

Let’s see for ourselves. 

Solomon also has a couple collation-related items, including unexpected silent truncation when working with UTF-8 collations.