TSQL Multi Column Unique Constraint That Also Allows Multiple Nulls

Question

I am currently doing some migration from MS Access to SQL Server. Access allows multiple Nulls in unique indexes where as SQL Server does not... I've been handling the migration by removing the 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 having is that I am not sure how to implement a composite or multi-column "filtered" indexes... or if this is really possible as I've found no examples in researching it.

I do have an idea to implement it by creating filtered indexes like so:

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 if this would even work let alone be the best method. Guidance in the right direction would be greatly appreciated.

Answer 1

You can add the following index to index only non nullable columns:

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)