Opinion: Don't write CREATE UNIQUE INDEX (ok, well not too often)

The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness.

I have a simple rule on this. Wherever possible business rules like uniqueness, check values, etc. should be part of the design of the table, and not enforced in an external object like an index.

So, rather than a unique index, I’d rather see a unique constraint on the underlying table.

But that’s where real life steps in. I see two scenarios that lead me to occasionally use CREATE UNIQUE INDEX.

SQL Server and NULL

I mentioned the other day that SQL Server’s handling of NULL is pretty good. The area where it really falls down though is how uniqueness is treated with NULL values. If I create a unique constraint (or a unique index) on a table, SQL Server enforces the uniqueness but it also insists that only one row can be NULL. That’s not correct and it’s treating a value that’s NULL like it is a value.

If a column is nullable and is constrained as unique, the database engine should only enforce uniqueness on rows that do contain a value. The SQL Server variation of this is really painful and I wish it could be changed.

To work around the SQL Server limitation, you can CREATE UNIQUE INDEX on your table but make it a filtered index with a WHERE clause that makes it apply only to non-NULL values. This allows us to apply the rule of “if there’s a value, then it’s unique”.

INCLUDE Columns

When SQL Server creates a unique constraint, it creates an index to support that constraint. With other non-clustered indexes in SQL Server, we have the option to INCLUDE columns (ie: they are only present at the leaf level of the index) to help to create covering indexes, for performance reasons.

SQL Server doesn’t allow us to add included columns to a unique constraint. So this is another reason why I might say CREATE UNIQUE INDEX because I then get the opportunity to INCLUDE columns as well.

Wish List

In a perfect world, I’d prefer to have unique constraints that worked properly, and could also have included columns, all as part of the table design, not as external indexes.

2018-03-27