The proper way to implement unique constraint that

问题:

I need 1 column in the table to hold unique non-null values or NULL. TSQL UNIQUE constraint treats 2 NULLs as equal, so I cannot make column unique.
What is the right way to handle this problem?
After doing some research, I found 2 methods which seem correct to me, but I cannot determine which one is better.
The first which is not applied to all cases:

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 ));

It works but requires all allowed values of null_or_unique_id to be non-negative (that's ok in my case)
The second one :

 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]);

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.

What is the best practice for such a case?
Thanks for your answers.

回答1:

4 ways:

• Filtered index (SQL Server 2008) <- recommended based on your tags
• Trigger (mentioned)
• Indexed view (in your question)
• Unique constraint/index with computed column (in your question)

回答2:

SQL 2008 allows you to define a filtered index - essentially an index with a WHERE clause - see Phil Haselden's asnwer to this question for MSDN links and an example.

回答3:

Normalise it. Move the column to a new table together with your current table's primary key. Make the column unique and not null in the new table. Nullable unique constraints make no logical sense and are of little or no practical use.