title: Add non-nullable columns to an existing table in SQL server?
body: <p>I already have a table which consists of data. I need to alter the table to add two new columns which are not null. How can I do that without losing any existing data?</p>
  
  <hr>
  
  <p>Here's what I tried (via right-clicking the table and selecting Design):</p>
  
  <ol>
  <li><p>Added new columns 'EmpFlag' (bit, null), 'CreatedDate' (datetime,
  null)</p></li>
  <li><p>Updated 'EmpFlag' column in the table, to have some valid values. (Just wanted to work on one field, so I didn't update 'CreatedDate' field)</p></li>
  <li><p>Now right clicked table, design, and made it not null. </p></li>
  </ol>
  
  <p>When I tried to save, this error message appeared:</p>
  
  <blockquote>
    <p>Saving changes is not permitted. <strong>The changes you have made require
    the following tables to be dropped and re-created</strong>.</p>
  </blockquote>