SQL Server can guarantee unique values in two ways, Primary keys and a unique indexes/constraints.  The primary difference between the two is that fields bound to a unique index or constraint can be NULL while a primary key field can not.  For the purpose of the unique indexes and constraints, NULL values are treated equal.  That means that you cannot have two NULL values.

Fortunately, there is a workaround for that will allow you to store multiple NULL values while ensuring that non-NULL values are unique.  We will utilize a schema bound view in order to facilitate this.  We will also create an index on the startdate column to help with the performance of the indexed view.

First let's create a table to test with.  In order to prevent conflicts with existing tables and databases, we suggest that you create these objects either in TempDB or in a database specific for testing.  In case you do not have the privileges to create your own database, we have added a partial GUID to all of the object names.

CREATE TABLE EMPLOYEE_9F2E916313EB
  (employee_id int not null identity(1,1)
  ,employee_lastname nvarchar(32) not null
  ,employee_firstname nvarchar(32) not null
  ,employee_startdate datetime null
  ,constraint pk_EMPLOYEE_9F2E916313EB primary key clustered (employee_id)
  )
GO
create index idx_EMPLOYEE_9F2E916313EB_startdate on EMPLOYEE_9F2E916313EB (employee_startdate)

While this may not be a very practical example, let's pretend that we want no two employees to have the same start date.  But, we may not know the start date of certain employees so the column has to allow null values.

Next let's create the view and the UNIQUE CLUSTERED index.

CREATE VIEW EMPLOYEE_9F2E916313EB_vw_startdate
with schemabinding
as
select employee_startdate
from dbo.EMPLOYEE_9F2E916313EB
where employee_startdate is not null
GO
create unique clustered index uidx_EMPLOYEE_9F2E916313EB_vw_startdate_startdate 
  on dbo.EMPLOYEE_9F2E916313EB_vw_startdate (employee_startdate)

An important thing to note is that you must specify the two part name for the object.  For SQL Server 2000 you need to specify the object owner, for SQL Server 2005 you must specify the schema.  The key to making a schema bound view is the option WITH SCHEMABINDING in the views declaration.  Also, an indexed view MUST have a clustered index created on it.

Now let's see how the database allows for multiple NULL values.

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','John' ,NULL)
insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Mike',NULL)
GO
select * from dbo.EMPLOYEE_9F2E916313EB

The result should look like this:




Now we will add more data that contains non-NULL dates.

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Tim','2009-02-19')
insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Bob','2009-02-20')
go
select * from dbo.EMPLOYEE_9F2E916313EB

The results should look like this:

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values('Smith','Ralph','2009-02-20')
go
select * from dbo.EMPLOYEE_9F2E916313EB

alter table EMPLOYEE_9F2E916313EB ADD login_name nvarchar(32) null
go
alter table EMPLOYEE_9F2E916313EB drop column employee_lastname
go
alter table EMPLOYEE_9F2E916313EB alter column employee_startdate datetime null

drop view EMPLOYEE_9F2E916313EB_vw_startdate
drop table EMPLOYEE_9F2E916313EB