Unique indexes and ANSI_NULLS in SQL Server and Azure SQL!

How to ensure uniqueness and allow NULLs with ANSI_NULLS set to OFF!

Introduction

I recently came across an interesting case involving the use of a UNIQUE constraint with some special considerations in a SQL Server or Azure SQL table.

The goal was to enforce uniqueness in a column while still allowing multiple NULL. To illustrate, let’s take a simplified example of a table called dbo.EmailTable. This table stores basic contact information such as first name, last name, and email. The Email column is optional, but when present, it must be unique. If no value is provided, the field is left as NULL.

CREATE TABLE dbo.EmailTable
(
  ID INTEGER IDENTITY(1, 1) NOT NULL,
  FirstName VARCHAR(32) NOT NULL,
  LastName VARCHAR(32) NOT NULL,
  Email VARCHAR(64) NULL
);
GO

The best solution in this scenario is a filtered unique index on the Email column. This type of index enforces uniqueness only for non-NULL values, which is exactly what we want.

CREATE UNIQUE NONCLUSTERED INDEX IX_UQ_EmailTable_Email ON dbo.EmailTable
(
  [Email]
)
WHERE
  (Email IS NOT NULL);
GO

This command works correctly if both the table and the index are created with the ANSI_NULLS option set to ON. Otherwise, SQL Server throws the following error.

Msg 1934, Level 16, State 1, Line 23
CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_NULLS'...

This error makes it clear that the index cannot be created when ANSI_NULLS is set to OFF. This setting controls how SQL Server handles comparisons involving NULL, including equality (=), inequality (<>), and so on.

The problem in practice

In our test environment, we used the actual application responsible for managing contact information. The application connects to the database with ANSI_NULLS set to OFF, and unfortunately, this setting cannot be changed. Any INSERT statements executed from the application make the same error.

Msg 1934, Level 16, State 1, Line 29
INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'...

So even though the index was correctly created with ANSI_NULLS ON, insert operations from the application failed due to the session setting. This meant we needed an alternative approach.

Back to the requirements

Here’s what we needed:

• Enforce uniqueness on a column when a value is provided
• Allow multiple NULL
• Work with the ANSI_NULLS OFF setting on the database connection

We decided to build a solution using a CHECK CONSTRAINT that references a scalar function. The function needed to:

• Check whether a value is unique within the table
• Accept multiple NULL values (i.e., consider them valid)
• Work correctly with ANSI_NULLS OFF on the database connection

Here’s the scalar function we created:

CREATE OR ALTER FUNCTION dbo.fn_EmailIsUniqueOrNull(@Email VARCHAR(64))
RETURNS BIT
AS
BEGIN
  IF @Email IS NULL
    RETURN 1;

  IF (
      SELECT COUNT(*) 
      FROM dbo.EmailTable 
      WHERE Email = @Email
     ) <= 1
    RETURN 1;

  RETURN 0;
END;
GO

This function works inside a CHECK CONSTRAINT because it is deterministic, doesn’t reference external objects, and behaves correctly with ANSI_NULLS OFF. The COUNT <= 1 logic is important because CHECK CONSTRAINTS are evaluated after the data is inserted so the new row is already in the table when the function is called.

The following execution plan is for an insert into the dbo.EmailTable table with the check constraint created, we observe that the Assert operator performs the domain integrity consistency check after the Table Insert operator.

Performance considerations

While the filtered index is still the best and most efficient solution, it cannot be used in environments where ANSI_NULLS must be OFF.

Using a user-defined function (UDF) in a CHECK CONSTRAINT may introduce performance overhead during mass inserts, because the function is executed for every row (starting with SQL Server 2019, scalar UDF inlining can help, but not in this particular scenario). That said, this table doesn’t expect massive insert activity, so the trade-off is acceptable.

We can now create the CHECK CONSTRAINT using the function dbo.fn_EmailIsUniqueOrNull.

ALTER TABLE dbo.EmailTable ADD CONSTRAINT CK_EmailTable_Email CHECK
(
  dbo.fn_EmailIsUniqueOrNull(Email) = 1
);
GO

Sample data validation

You can test the constraint with the following INSERT and UPDATE operations.

INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Crystie', 'Tibald', NULL);
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Spencer', 'Bras', 'sbras1@sfgate.com');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Kaiser', 'Bachellier', NULL);
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Farrell', 'Nehlsen', 'fnehlsen3@slate.com');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Marleah', 'Paydon', 'mpaydon4@nih.gov');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Mireille', 'Mazzey', 'mmazzey5@edublogs.org');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Jennilee', 'Moorrud', 'jmoorrud6@apache.org');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Vivian', 'Michelle', NULL);
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Haydon', 'Saller', 'hsaller8@list-manage.com');
INSERT INTO dbo.EmailTable (FirstName, LastName, Email) VALUES ('Kailey', 'Allmann', 'kallmann9@jigsy.com');

UPDATE dbo.EmailTable SET Email = 'kallmann91@jigsy.com' WHERE Email = 'kallmann9@jigsy.com';
UPDATE dbo.EmailTable SET Email = NULL WHERE Email = 'jmoorrud6@apache.org';

Summary

Managing conditional uniqueness for NULL values in SQL Server might seem simple, but when ANSI_NULLS must remain OFF, filtered indexes are not an option. In these situations, a deterministic scalar function combined with a CHECK CONSTRAINT offers a practical alternative, enforcing business rules without the need for triggers or complex logic.

While this solution introduces a slight performance cost during inserts or updates, it is a safe and effective strategy in environments where data volume is moderate. As always, the key is to balance data correctness with performance, based on the application’s specific requirements.

The goal was to enforce uniqueness on a column while still allowing multiple NULL values. To illustrate, let’s take a simplified example of a table called dbo.EmailTable. This table stores basic contact information such as first name, last name, and email. The Email column is optional, but when present, it must be unique. If no value is provided, the field is left as NULL.