title: Set a column on a populated table to not allow nulls where lots of rows already are set to null
body: <p>I've got a table with an active column of type bit that is mostly set to null. The active column was added as an after thought and mistakenly set to allow nulls. So SQL Server won't let me turn off the null flag because most of the fields are already set to null. </p>
  
  <p>I tried setting a default value of 1 to the column and then removing the null flag but it wasn't having any of that. I think the default value only applies to inserts and not an alter.</p>
  
  <p>I then tried to set all the null values to 1 explicitly with the following query:</p>
  
  <pre><code>Update cnc.aidPage set active = 1 where active = null
  </code></pre>
  
  <p>Unfortunately this doesn't affect any rows. I tried derivatives of this and found that it doesn't change anything only when the filter is trying for null. So the following work:</p>
  
  <pre><code>Update cnc.aidPage set active = 1 where active = 0
  Update cnc.aidPage set active = null where active = 1
  </code></pre>
  
  <p>I would appreciate it if anyone could tell me why the filter doesn't work if I check for null or if there is an easier way to accomplish turning allow nulls off.</p>