title: Best way to add a new column with an initial (but not default) value?
body: <p>I need to add a new column to a MS SQL 2005 database with an initial value.  However, I do NOT want to automatically create a default constraint on this column.  At the point in time that I add the column the default/initial value is correct, but this can change over time.  So, future access to the table MUST specify a value instead of accepting a default.</p>
  
  <p>The best I could come up with is:</p>
  
  <pre><code>ALTER TABLE tbl ADD col INTEGER NULL
  UPDATE tbl SET col = 1
  ALTER TABLE tbl ALTER COLUMN col INTEGER NOT NULL
  </code></pre>
  
  <p>This seems a bit inefficient for largish tables (100,000 to 1,000,000 records).  </p>
  
  <p>I have experimented with adding the column with a default and then deleting the default constraint.  However, I don't know what the name of the default constraint is and would rather not access sysobjects and put in database specific knowledge.</p>
  
  <p>Please, there must be a better way.</p>