Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty existing table!

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.

ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO

You should change it to these statements in SQLServer2000:

ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO

ah, ridiculous! right?

Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.Hi,

You can use a workaround in this case... Put a DEFAULT constraint on
your column and it will work...

Enjoy,

Cdric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
aling a crit :

Quote:

Originally Posted by

Damn! SQLServer2000 can't add a NOT NULL COLUMN even in one empty
existing table!
That is, A is the existing table and it is emtpy, I want to add one NOT
NULL COLUMN (col_new) to A using following T-SQL statement, then it
will fail.
>
ALTER TABLE A ADD
col_new varchar(600) NOT NULL
GO
>
You should change it to these statements in SQLServer2000:
>
ALTER TABLE A ADD
col_new varchar(600) NULL
ALTER TABLE A ALTER COLUMN col_new varchar(600) NOT NULL
GO
>
ah, ridiculous! right?
>
Fortunately, this stupid behavior is changed in SQLServer2005. The
first T-SQL statements works.