TSQL Multi Column A unique constraint that also allows multiple zeros

Question

I am currently migrating MS Access to SQL Server. Since Access allows multiple Nulls on unique indexes where SQL Server does not work ... I handled the migration by removing indexes in SQL Server and adding filtered indexes:CREATE UNIQUE NONCLUSTERED INDEX idx_col1_notnull ON tblEmployee(col1) WHERE col1 IS NOT NULL;

The problem I am facing is that I am not sure how to implement compound or multi-column “filtered” indexes ... or if it is really possible, since I did not find any examples for research.

I have an idea to implement it by creating filtered indexes as follows:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1col2_notnull ON tblEmployee(col1,col2) WHERE col1 IS NOT NULL

And then adding a second filtered index:

CREATE UNIQUE NONCLUSTERED INDEX idx_col2col1_notnull ON tblEmployee(col1,col2) WHERE col2 IS NOT NULL

But I'm not sure that this will even work, not to mention the best method. Leading in the right direction would be very helpful.

Answer 1

You can add the following index to index only columns with invalid values:

create table tblEmployee(col1 int, col2 int)
go

create unique nonclustered index idx_col1col2_notnull ON tblEmployee(col1,col2) 
where col1 is not null and col2 is not null
go

--This Insert successeds
insert into tblEmployee values
(null, null),
(null, null),
(1, null),
(1, null),
(null, 2),
(null, 2)

--This Insert fails
insert into tblEmployee values
(3, 4),
(3, 4)

Answer 2

You can also combine multiple indexes to preserve unique constraints in all fields where the NULL field can be NULL

CREATE TABLE MyTable (
    [Idx_1] [int] NOT NULL,
    [idx_2] [int] NOT NULL,
    [idx_3] [int] NULL,
    [no_index_field] [nvarchar](50) NULL
) ON [PRIMARY]
GO


CREATE UNIQUE NONCLUSTERED INDEX idx_3_fields
ON MyTable (Idx_1, Idx_2, Idx_3)
WHERE Idx_3 IS NOT NULL;

CREATE UNIQUE NONCLUSTERED INDEX idx_2_fields
ON MyTable (Idx_1, Idx_2)
WHERE Idx_3 IS NULL;