Replacing Unique Constraints With Unique Indexes In SQL Server

Wouldja Shouldja?

---

I do love appropriately applied uniqueness. It can be helpful not just for keeping bad data out, but also help the optimizer reason about how many rows might qualify when you join or filter on that data.

The thing is, I disagree a little bit with how most people set them up, which is by creating a unique constraint.

Unique constraints are backed by nonclustered indexes, but they’re far more limited in what you can do with them.

For example, you can’t apply a filter definition, or have any included columns in them. And a lot of the time, those things make the data you’re identifying as unique more useful.

Included Battery

---

Here’s what I see quite a bit: A unique constraint on a single key column, and then a nonclustered index on that column, plus included columns.

So now you have two indexes on that column, only one of them is unique, and only of them gets used in queries as a data source.

The unique constraint may still get used for cardinality estimation, but the structure itself just sits around absorbing writes all the live long day.

In this case, you’re almost always better off using a unique nonclustered index with includes instead.

Sure, this doesn’t work if you have one column that needs to be unique, but you want multiple columns in the key of a nonclustered index, but that’s not what I’m talking about here.

Feelings Filter

---

That you can add a where clause to indexes is still news to some people, and that’s fine.

Often they’re used to isolate and index certain portions of your data that are frequently accessed, or that benefit from having a statistics histogram built specifically on and for them, which don’t have any of their 201 steps tainted or influenced by data outside of the filter.

The latter scenario is good for skewed or lumpy data that isn’t accurately depicted in a histogram on a full (probably rather large table) even with a full scan.

But another good use is filtering data down to just the portion of unique data that you care about. An example is if your table has multiple rows for a user’s sessions, but only one session can be active. Having a unique filtered index on users, filtered to just what’s active, can get you down to just the stuff you care about faster.

Clean Up

---

If you ever run sp_BlitzIndex and see duplicate or borderline duplicate indexes, some of them may be on unique constraints or indexes.

Don’t be afraid to merge semantically equivalent constraints or indexes together. Just be sure to obey the rules of key column order, and all that.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.