A practical use for filtered indexes

Have you ever wanted to create a table in SQL Server with a nullable column that should be unique as well? I have often needed this and resorted to defining a new table that links to the master table. But SQL Server 2008 has the solution to this, see my example below.

CREATE TABLE [dbo].[Person] (
      [Id]              INTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      [FirstName]       NVARCHAR(50) NOT NULL,
      [LastName]        NVARCHAR(50) NOT NULL,
      [EmailAddress]    VARCHAR(256) NULL
);

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_0] UNIQUE NONCLUSTERED ([LastName], [FirstName]);

Next consider adding a unique constraint or unique index on the EmailAddress column to make sure its unique.

ALTER TABLE [dbo].[Person]
      ADD CONSTRAINT [AK_Person_1] UNIQUE NONCLUSTERED ([EmailAddress]);

So far so good, we can now add some test data.

INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Smith’, ‘[email protected]’);
INSERT INTO [dbo].[Person] VALUES (N’Bob’, N’Jones’, ‘[email protected]’);
INSERT INTO [dbo].[Person] VALUES (N’Mary’, N’Smith’, NULL);

Next try insert another person with no email address.

INSERT INTO [dbo].[Person] VALUES (N’Frank’, N’Smith’, NULL);

Whoops, looks like our unique constraint is not going to let us have more than one NULL in the table. Which is correct. So how can we fix this, well this is where the new SQL Server 2008 filtered indexes come in. We simply create an index with a WHERE clause as follows:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Person_EmailAddress]
      ON [dbo].[Person] ([EmailAddress])
      WHERE [EmailAddress] IS NOT NULL;

And then we can insert our new record without having the problem as we did with the unique constraint.

Unfortunately there is a limitation to how this can be used. A unique index cannot be used in a foreign key constraint. I just wish the SQL Server team would add the WHERE clause, and while they are at it the INCLUDE clause as well, to unique constraints.

Thanks to Gail for the suggestion.