SQL Server: specifying a unique optional column

SQL Server has a quirk with optional unique columns: you cannot have more than one null in a table.

For example:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) unique  --  optional, but unique
);

(In Australia, we have a Tax File Number, which is unique to the tax payer).

The following is a workaround:

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9)
);
CREATE UNIQUE NONCLUSTERED INDEX uq_employees_tfn ON employees(tfn)
WHERE tfn IS NOT NULL;

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Is it possible to name a unique constraint and give it the above condition is a manner similar to the first example? That is, is it possible to include the more complex constraint inline with the column definition?

Yes, the following works (only in SQL Server 2016 onwards):

create table employees (
    id int identity(1,1) primary key,
    givenname nvarchar(24) not null,
    familyname varchar(24) not null,
    tfn char(9) NULL 
        INDEX uq_employees_tfn 
        UNIQUE (tfn) 
        WHERE tfn IS NOT NULL
);

The inline syntax results in the same unique filtered index as for the separate CREATE UNIQUE INDEX. Subsequent DDL will need to manage it like an index rather than a constraint.

It is not brilliantly clear in the CREATE TABLE documentation, but is illustrated in Example S.

StackExchange Database Administrators Q#201571, answer score: 8