SQL Server: specifying a unique optional column
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.