The standards-compliant behaviour for unique indexes on nullable columns is that non-null values have to be unique, but you can have as many null values as possible.

Unfortunately, Microsoft did not do that.

Fortunately, there is a fairly simple work-around that can be used when defining the index https://dba.stackexchange.com/a/80516):

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

Hopefully we can use that in lib/ddl/mssql_sql_generator.php.

Presumably, we don't currently have any indexes like that in core Moodle, or users would be getting errors, but it would be good to check that assumption by checking the install.xml files. (If there were any, we would need to drop them and re-recreate htem in an upgrade step. Probably only if dbfamily == mssql.)