I didn't see anything in the ALTER COLUMN documentation indicating that a change from NULL to NOT NULL wouldn't work--what am I missing? :unsure:
Good fun question that points up (i) a definite error in BoL for 2008 R2 (or maybe a bug in 2008 R2 if what's in BoL is what it is supposed to do) and (ii) a possible error in BoL for 2012.
Maybe the BoL error is part of why there are so many incorrect answers (87% wrong to date; 67% agreed with th BoL error).
The reference in the explanation is to the SQL 2012 version, although the question says it was only tested with the 2008 R2 version; as the 2012 version of the page explicitly states that a column can be changed from NULL to NOT NULL provided there are currently no NULL values in the column
NOT NULL can be specified in ALTER COLUMN only if the column contains no null values
this is a bit unfortunate. I haven't tested with 2012 (must acquire a platform that will run 2012) and I suspect that this is an error on that BoL page.
Of course referring to the 2012 page is harmless here because the 2008 R2 version has exactly the same statement. For 2008 R2 this is definitely false, since a column which is indexed can not be changed from NULL to NOT NULL even if the table is empty - the only way to get around this is to drop the index, alter the column, and recreate the index. Both the 2008 R2 and 2012 versions give the same code example of updating the table to set nulls to some non-null default and then using alter column, which just doesn't work (at least in 2008 R2) if the column is indexed.
edit: bitten by the "quote the wrong comment" bug; edited to fix.