Designing a rock solid application and database can be difficult. New features and overlapping features mean that you’re able to accomplish a single task in many ways. For instance the numeric and decimal data types. Which should you use?

While on the surface it would seem that unique constraints  and unique indexes are very different they actually achieve the same goal and there are some things to be aware of.

The comparison

Unique constraints apply a unique index; however, this index is very limited. It doesn’t have any included columns.

Let’s say that you have a query causing lot’s of Key Lookups. You already have a Unique constraint on the column and can see the unique index is being used. If you want to add more columns to “cover” this query, you can’t. You’d need to add a duplicate index.

Another difference is how errors are reported. Attempts to insert a duplicate into an object with a unique constraint will result in error 2627. This same behavior is covered with a unique index; however, the error code is 2601.

Summary

Unique constraints and Unique indexes overlap and accomplish the same goal. In the event that you have many queries causing lookups you may want to convert the unique constraint to a unique index that allows included columns. Just be sure to change any Catch blocks in your application to adjust for the different error code.

Be sure to check out my other posts at:

SQL Server Central & SQLTechBlog.com

Also be sure to follow me on LinkedIn and Twitter!