title: The proper way to implement unique constraint that allows multiple NULL values in SQL Server
body: <p><br>
  I need 1 column in the table to hold unique non-null values or <code>NULL</code>. TSQL <code>UNIQUE</code> constraint treats 2 <code>NULL</code>s as equal, so I cannot make column unique.<br>
  What is the right way to handle this problem?<br>
  After doing some research, I found 2 methods which seem correct to me, but I cannot determine which one is better.<br>
  The first which is not applied to all cases: </p>
  
  <pre><code>CREATE TABLE test (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
     null_or_unique_id INT, unique_key AS  
  (CASE WHEN [null_or_unique_id] IS NULL THEN -(1)*[id] 
   ELSE [null_or_unique_id] END), UNIQUE(unique_key ));
  </code></pre>
  
  <p>It works but requires all allowed values of <code>null_or_unique_id</code> to be non-negative (that's ok in my case)<br>
  The second one :</p>
  
  <pre><code> CREATE VIEW test_view WITH SCHEMABINDING AS
   SELECT [null_or_unique_id] FROM dbo.test WHERE [null_or_unique_id] IS NOT NULL;
   GO
   CREATE UNIQUE CLUSTERED INDEX byNullOrUniqueId 
   ON dbo.test_view([null_or_unique_id]);
  </code></pre>
  
  <p>Surely, it's also possible to implement the desired functionality with triggers, but I think trigger solution will create more overhead then any of mentioned above. </p>
  
  <p>What is the best practice for such a case?<br>
  Thanks for your answers.</p>