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

[aling]

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.

[=?iso-8859-1?q?C=E9dric_Del_Nibbio?=]

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

Hi,

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

Enjoy,

Cédric Del Nibbio
MCSD .NET
MCTS SQL Server 2005

http://cedric-delnibbio-sql.blogspot.com

aling a écrit :

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.