<< people who baffle me | Home | so long Max >>

ALTER TABLE difference in SQL 2005

I think I may have found an undocumented difference between SQL Server 2000 and SQL Server 2005.  When you attempt to add a non-null column without a default to an empty table in SQL Server 2000, for some stupid reason it won't let you if you use ALTER TABLE ADD [column].  You are forced to drop and recreate the table with the new column added to the CREATE TABLE statement.  This appears to be different than SQL Server 2005, which wisely does allow such a command to execute without error.  I haven't found documentation of this anywhere.

Print | posted on Tuesday, August 07, 2007 5:23 AM

Comments on this post

# re: ALTER TABLE difference in SQL 2005

Requesting Gravatar...
It should only allow you to add the column if the table is empty. You can add the column with a default and the drop the default afterward but the non-null column will need values if the table is not empty when you try to add it.
Left by ManicArchitect on Apr 09, 2008 2:25 PM

# re: ALTER TABLE difference in SQL 2005

Requesting Gravatar...
could share with me the differences? thanks.
Left by wyng on Oct 23, 2008 3:40 PM

Your comment:

 (will show your gravatar)
 
Please add 4 and 6 and type the answer here: