SQL Server:

How to do it:

1. The best way is a filtered index. Uses DRI
   SQL Serfer 2008+

2. Computed column with uniqueness. Uses DRI
   See Jack Douglas' answer. SQL Server 2005 and before

3. An indexed/materialised view which is like a filtered index. Uses DRI
   All versions.

4. Trigger. Uses Code, not DRI.
   All versions

How not to do it:

1. Check constraint with a UDF. This isn't safe for concurrency and snapshot isolation.
   See One Two Three Four

SQL Server:

How to do it:

1. The best way is a filtered index. Uses DRI
   SQL Server 2008+

2. Computed column with uniqueness. Uses DRI
   See Jack Douglas' answer. SQL Server 2005 and before

3. An indexed/materialised view which is like a filtered index. Uses DRI
   All versions.

4. Trigger. Uses Code, not DRI.
   All versions

How not to do it:

1. Check constraint with a UDF. This isn't safe for concurrency and snapshot isolation.
   See One Two Three Four