I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.

As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.

Thank God 2008 finally implemented something Access has had since 2003. 🙂