Failed to add "NOT NULL" column to empty table in SQL Server

Question

I understand that when adding a column to a table containing data on the SQL server, the column must have a NULL parameter or a default value. Otherwise, could SQL Server place new rows with?

I do not understand why I cannot add a NOT NULL column to an empty table. I tried this on two instances of SQL 2008 and one instance of SQL 2005 with no problems. However, a client with SQL 2000 has this problem. Is it related to SQL 2000 or is it an option that you can disable. Let me hope this is an option.

Select @@Version

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) 12/17/2002 14:22:05 Copyright (c) 1988-2003 Microsoft Developer Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Select count(*) from actinv

0

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NOT NULL

Msg 4901, 16, 1, 1 ALTER TABLE , DEFAULT. "BATCHNUMBER" "ActInv", NULL DEFAULT .

Answer 1

SQL Server 2000 . , , - SQL Server 2005/2008.

SQL Server 2000 :

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NOT NULL CONSTRAINT ActInv_Temp DEFAULT 'foo'
ALTER TABLE [ActInv] DROP CONSTRAINT ActInv_Temp

go

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NULL 
ALTER TABLE [ActInv] ALTER COLUMN [BATCHNUMBER] NVARCHAR(50) NOT NULL 

Answer 2

, , NULL, DEFAULT.

, :

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) DEFAULT 'foo' NOT NULL

: , SQL Server (2000 2005). , , / . SQL Server 2000.

Answer 3

2000, , , , ?

Answer 4

2 :

ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) 
GO

ALTER TABLE [ActInv] ALTER COLUMN [BATCHNUMBER] NVARCHAR(50) NOT NULL
GO

, 2000 ( 2005 )