Using a CHECK Constraint to Enforce a Trigger’s Unique Value

I have a column in a SQL Server 7.0 table that allows NULL values. I want this column to be unique when it has a value other than NULL. What's the best way to program this behavior? If I put a UNIQUE constraint on the column, I can include only one record that has a NULL value. I'm using a trigger to enforce this restriction, but can you recommend a simpler way to ensure that all non-NULL values are unique?

SQL Server has no built-in mechanism to prohibit duplicates except NULLs, so you need to implement a custom CHECK constraint to enforce this restriction. For example, Listing 1, page 64, shows a code snippet that enforces the kind of integrity you're looking for. In SQL Server 2000, you can use INSTEAD OF triggers as well to carry out this enforcement. For information about INSTEAD OF triggers, see Itzik Ben-Gen, T-SQL Black Belt, "Tricks with INSTEAD OF Triggers," December 2000, InstantDoc ID 15828; and Kalen Delaney, Inside SQL Server, "INSTEAD OF Triggers on Views," December 2000, InstantDoc ID 15791, and Inside SQL Server, "INSTEAD OF Triggers," November 2000, InstantDoc ID 15524.