title: Unable to add a "NOT NULL" column to empty table in SQL Server
body: <p>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?</p>
  
  <p>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.</p>
  
  <pre><code>Select @@Version
  </code></pre>
  
  <p>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) </p>
  
  <pre><code>Select count(*) from actinv
  
  0
  
  ALTER TABLE [ActInv] ADD [BATCHNUMBER] NVARCHAR(50) NOT NULL
  </code></pre>
  
  <p>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.</p>