Enforcing the uniqueness of data in a table is a common requirement. For example an "Employees" table might contain an "EmployeeID" field that must uniquely identify each row. This is usually handled by creating a primary key or unique constraint on the field. This works well when the field in question is required. In our example each employee is assigned an employee ID number. Sometimes however this is not sufficient to cover all circumstances. Let us assume that each employee may be assigned a company email address, but not all employees will be given an email address. No two employees can share the same email address. We want the "EmailAddress" field in the Employees table either to be empty or contain a value not already in the table. We can enforce the uniqueness of the email address values either by using an indexed view or by creating an index.
Here is the T-SQL used to create the Employees table we will use for our examples.
CREATE TABLE Employees
(
EmployeeID CHAR(4) NOT NULL PRIMARY KEY,
LastName VARCHAR(20) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
EmailAddress VARCHAR(30) NOT NULL DEFAULT ''
);
Option 1: Using an Indexed View
We can create a view of Employee records with an email address then create a unique index on the EmailAddress field. If a user tries to insert a duplicate email address the insert will fail.
Here is the T-SQL used to create the view and index. Note that the view only contains records which have a non-empty email address.
CREATE VIEW EmployeeEmailAddresses WITH SCHEMABINDING
AS
SELECT EmailAddress
FROM dbo.Employees
WHERE EmailAddress != '';
GO
CREATE UNIQUE CLUSTERED INDEX Index_EmployeeEmailAddresses_EmailAddress
ON EmployeeEmailAddresses ( EmailAddress );
We can then insert some records into the Employees table using the T-SQL statements below.
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E200', 'Pond', 'Amy', '' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E400', 'Jones', 'Martha', '' );
-- this insert will fail
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );
The last insert will fail because a record already exists with the email address "smith@example.com". This approach will work in both SQL Server 2005 and 2008.
Option 2: Create an Index
SQL Server 2008 introduced the WHERE clause into the syntax for the CREATE INDEX statement. We can create a unique index on records which are not empty with the T-SQL statement below.
CREATE UNIQUE INDEX Index_Employees_EmailAddress
ON Employees ( EmailAddress )
WHERE ( EmailAddress != '' );
To test this index first drop the indexed view and empty the Employees table, then insert our test data again. The last insert will fail.
-- this will drop the indexed view we created earlier if it exists
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'EmployeeEmailAddresses' )
BEGIN
DROP VIEW EmployeeEmailAddresses;
END;
-- empty the Employees table
DELETE
FROM Employees;
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E200', 'Pond', 'Amy', '' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E400', 'Jones', 'Martha', '' );
-- this insert will fail
INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )
VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );
This technique is new to SQL Server 2008 and is not backwards compatible.
For more information on each approach consult the SQL Server documentation.
References
Create View from SQL Server Books Online:
Create Index from SQL Server Books Online:
