ALTER TABLE difference in SQL 2005

The Wrecking Bawl Destructuring query language, one keyword at a time.

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

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. Posted on Tuesday, August 7, 2007 5:23 AM all tech stuff , SQL Server | Back to top

Comments on this post: ALTER TABLE difference in SQL 2005

# re: ALTER TABLE difference in SQL 2005

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

could share with me the differences? thanks.

Left by wyng on Oct 23, 2008 3:40 PM

# re:adding new column

how to add new column between the exixting columns in the table?

Left by velava on Jul 09, 2009 7:17 AM

Your comment:

Title:

Name:

Email: (never displayed) (will show your gravatar)

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Verification: