title: Create Unique Index One/Only/Single NULL
body: <p>It seems that in SQL Server, unique indexes treat NULLs as 'just another value' instead of like in the rest of SQL where comparisons against NULL return NULL.</p>
  
  <p>Say you've got a table (<code>t</code>) with a unique index on a nullable column <code>K</code>:</p>
  
  <pre><code>K     V
  0     32
  1     12
  3     45
  </code></pre>
  
  <p>All good.</p>
  
  <p>But it will also allow </p>
  
  <pre><code>K     V
  0     32
  1     12
  3     45
  NULL  89     &lt;-- Baaad
  </code></pre>
  
  <p>And vice versa, it will also allow the following:</p>
  
  <pre><code>K     V
  NULL  89
  0     32    &lt;-- not good
  </code></pre>
  
  <p>I can see this is could be a potential disaster as I'm using NULLs key values to represent values where no further break down is possible - having a total and a breakdown leads to double counting or inconsistency.</p>
  
  <p>I can find seemingly thousands of questions where where people want to do the opposite (allow multiple NULLs), but none that want to treat NULLs as NULLs.</p>
  
  <hr>
  
  <p>How can I get SQL Server to treat NULLs as NULLs (and only allow one NULL <em>or</em> any number of unique values in a column) in a  unique index?</p>