MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1562

Thanks for the info.

I always thought that one reason to use a constraint is so that the engine can decide for itself whether it needs to create an index or not. E.g. if the table is very small (fits on a single data page for example) then an index may not be the best way to enforce uniqueness. Am I wrong or is SQL Server just "lazy" to always create an index?

Alex

You are absolutely right. SQL Server is just lazy! It doesn't even allow you to drop the index without dropping the constraint.

I don't know if I'd personally classify the behavior as lazy.  The behavior has always been useful from my standpoint because I'll create a dummy PK on my tables for PK-FK relationship purposes and use UNIQUEs for singleton lookups which I'd want indexed anyway.  Also, remember that you can also use UNIQUEs as your "primary key" (i.e. as a natural key) instead of a PRIMARY KEY constraint so you'd likely want it indexed anyway.

Perhaps I should illustrate my point with an example. Suppose I have lookup (reference) table for gender. It contains entries for 'Male', 'Female', and perhaps 'unknown'. Since I like my PKs to be consistent I always give my tables a meaningless PK, so I need a unique constraint on the descripton.

But since the table is so small I doubt that it would benefit from any index, not even one on the PK. Any database engine that is smart enough could cache the whole table in memory (possibly with in-memory indexes created while caching the data) and for any retrieval operations it will most likely outperform any implementation that requires additional disk access such as physically persisted indexes. The table will also be faster to update because there are no persistent indexes to maintain.

Even if I would make the description the natural PK, I still don't see why an index would be usefull for anything but simplifying the code of the engine.

As the table grows (if it grows) then the engine would have the freedom to switch to using an index when that becomes more efficient.

The same would be true for any similar small table. And I usually have a whole bunch of those in my databases.

When I code for something like gender, I use a CHECK constraint on a nullable column checking for 'M' and 'F' rather than creating a PK-FK relationship.  I don't think it's necessary to create a PK-FK relationship in this scenario. The customer/employee/whatever row can only be 1 of 3 possible known, intuitive values, right?  I then let the front end or whatever does reporting handle the translation (i.e M = Male, F = Female, NULL = not known). Now if you have codes that may not be known by looking at them (i.e such as status codes, for instance), you'd probably be better served by creating descriptions and a PK-FK relationship which serve to better document just what the heck is going on.  

I believe I read somewhere (and I can't remember where) that the unique index is required because the engine needs it to actually enforce the uniqueness for the UNIQUE constraint (which just documents the logical definition of the table).  I'll have to hunt around and see if I can find where I read that. So, yes.  I agree with your point that the index is something that is needed to simplify the engine's searching of the data for duplicated values.

 Thanks for the response. I largely agree with your points. I think I would agree completely if the CHECK constraint could be incorporated in a domain (UDT) so that it can be re-used and if support for domains was a bit more mature (e.g. to allow the domain incl. CHECK constraint to be redefined).

In SQL 2000, I have used unique index instead on unique constraint as some of the tables to which I need to add uniqueness are already replicated. To alter a replicated table, you need to use sp_repladdcolumn, or sp_replDropColumn prcedures. I can't simply use ALTER TABLE... and there is no sp_replAddConstraint. At times I have used adding a constraint by adding a column and then dropping it. When you use index, you dont have any of these issues.
This issue is resolved in SQL 2005 and you can replciate schema changes directly.

It may seem like "lazy", but think again...There's the practical consideration that uniqueness constraints can be (and often are) declared when the table is empty. So, then, what basis does the database have for selecting a strategy to enforce uniqueness?  I can imagine some declarative options in physical design, but that just makes another decision and more work for the DBA.  What we really have is a very pragmatic approach to always create an underlying index in case it's needed.  Note that the existence of doesn't neccessarily mean the DB engine will use it.  Furthermore, in the classical small table case where an index is not needed, it is a very small price to pay to safeguard in case the table grows large.

It seems to me that a benefit of using a UNIQUE INDEX as opposed to a UNIQUE CONSTRAINT is that the index can have INCLUDED columns at the leaf level which can improve index efficiency.  Whereas you can't have included columns in a constraint definition.  Correct?

That's absolutely correct.  But, that's an advantage of any index, not necessarily a unique one.  The purpose of an index is to improve performance.  The purpose of a constraint is improve integrity.