Unable to add a "NOT NULL" column to empty table in SQL Server

sql-server sql-server-2000 null ddl

21,030

SQL Server 2000 does not check for an empty table. What you are seeing is an improvement in SQL Server 2005/2008.

Either of the following two step processes will make the change in SQL Server 2000 against an empty table:

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

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

21,030

Author by

user69374

Updated on July 01, 2020

Comments

user69374 almost 4 years
I understand that when adding a column to a table containing data in SQL server, the column must have a NULL option or a default. Otherwise what would SQL Server pad the new rows with?

I am at a loss as to why I can't add a NOT NULL column to an empty table however. I have tried this on two instances of SQL 2008 and one instance of SQL 2005 with no problems. However a customer with SQL 2000 does have this problem. Is this related to SQL 2000 or is it an option you can turn off. Let's hope it's an option.
```
Select @@Version
```
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation 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, Level 16, State 1, Line 1 ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'BATCHNUMBER' cannot be added to table 'ActInv' because it does not allow nulls and does not specify a DEFAULT definition.
- Neil N almost 15 years
  
  I know this sounds stupid, but is the table REALLY empty?
- user69374 almost 15 years
  
  Well I do Select count(*) from ActInv and it returns zero!
- Neil N almost 15 years
  
  Slect count(*) only returns records where at least one column is not null. I know, sounds silly, but try a select * from table instead.
- user69374 almost 15 years
  
  I never knew that! Select * returns zero rows though, I just checked.
- Neil N almost 15 years
  
  ok, I had to ask. Next, and this may sounds silly too, check the permissions of that table, do you have COMPLETE read/write access to it?
- ErikE almost 15 years
  
  Neil N, you are incorrect. create table b(a int) insert b values(null) select count(*) from b -- 1
Raj More almost 15 years

huh? he already stated that the table is empty - it seems to me like the question is: Why can you have a NOT NULL without Default Definition while creating a table but not be able to add NOT NULL to an empty table.
user69374 almost 15 years

The question is why does SQL2000 need a DEFAULT if there are no rows in the table? SQL2005 and SQL2008 don't!
user69374 almost 15 years

That SQL is generated by an update program. I was hoping this is an option you could turn off. It now looks like I'll have to modify the program to workround this in the way you suggest, which sucks.
markau over 6 years

This worked perfectly - except between ALTERing the table to add the nullable column, and ALTERing the table to NOT NULL, I had to UPDATE <table> SET newcol = <value>. SQL won't allow a nullable column, with all null values, to be converted to a NOT NULL column. Obvious really :-)
Shannon Severance over 6 years

@markau, This answer is specific to OP's issue where OP had an empty table on SQL Server 2000. With an empty table an update is a no-op, and not needed. SQL Server 2005 and later will check the table if adding a not null column, and go ahead if the table is empty.