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.
I am not sure what you mean by "properly." Are you comparing SQL Server to some standard or other product? What did you mean?
By "properly", I mean as per the standard, but specifically ones that test uniqueness only when values are present ie: not null. Any number of rows should be able to have those columns null, not a limit of only one row.
And yes many other DB engines implement this correctly.
I have to manage the performance of queries that "harness" a table with a filtered "unique" index. Most queries against the table start with IF EXISTS or SELECT DISTINCT or they use the filter as a predicate. Concurrency issues are common in my system, where the only solution is to use a restrictive transaction isolation level that wl support the app's need for consistent results.
Compared to a filtered "unique" index, for my particular situation the better solution would have been to normalize the design, by instead using a 1 to 1 relationships to N (in my case, 4) tables. The additional JOINs that will be needed is IMO a small price to pay when compared to my system's queries that use IF EXISTS and SELECT DISTINCT.
Sorry but can't say I followed what you meant. Perhaps the first question is why most queries start with IF EXISTS. Do you have an example?
If you have requirements for uptime deployments of index changes (e.g., using the ONLINE=ON option), your unique constraint will need to be expressed as an index.
I would prefer to be able to specify these constraints declaratively rather than using a physical construct, but I'm stuck with the current behavior of the engine for now.
Agreed but that means we should have an ONLINE option for adding constraints too