Should I Use a UNIQUE Constraint or a Unique Index in SQL Server?

By:   |   Comments (14)   |   Related: > Constraints


Problem

I am modifying a table and I need to add a column that must be defined as being unique. I see that SQL Server allows you to define a UNIQUE constraint on a column but I also see that you can create a unique index on a column. Which one should I use?

Solution

The differences between the two methods are very subtle. UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design. In addition, UNIQUE constraints can be created as part of a table's definition and, as a by-product, SQL Server will automatically create a unique index under the hood when the constraint is created. In contrast, defining unique indexes are part of designing the physical data model and they are not ANSI standard.

From a performance standpoint, UNIQUE constraints and unique indexes are effectively the same to the query optimizer and you will not see any performance benefit to using one vs the other. In my model, I stick to the ANSI standard and implement column uniqueness via constraints. In my opinion, UNIQUE constraints help in better documenting a table and I use them over defining unique indexes.

In a Management Studio connection, run the following script to create a table called CAR. The table defines a UNIQUE constraint on a car based on its make and model.

CREATE TABLE dbo.CAR
(
CARID INT IDENTITY(1,1) NOT NULL,
MAKE VARCHAR(10) NOT NULL,
MODEL VARCHAR(10) NOT NULL,
TOTALDOORS TINYINT NOT NULL,
TOTALCYLINDERS TINYINT NOT NULL,
CONSTRAINT PK_CAR PRIMARY KEY NONCLUSTERED (CARID),
CONSTRAINT UQ_CAR UNIQUE NONCLUSTERED (MAKE, MODEL)
)
GO

Running SP_HELP CAR shows that the engine has created both the constraint and a supporting index:

fig 1

A requirement has now crossed my desk. For reporting purposes, management wants to track cars by their year made, in addition to the make and model. We'll alter the table to include a YEAR column:

ALTER TABLE DBO.CAR
ADD [YEAR] SMALLINT
GO


Adding this column requires me to change the constraint's uniqueness otherwise I won't be able to add more than one car by MAKE and MODEL regardless of year. In addition, it's probably a good idea to not allow duplicate cars by make, model, and year. As a result, I'll re-create the constraint's unique index to include the year.

DROP INDEX DBO.CAR.UQ_CAR
GO
CREATE UNIQUE INDEX UQ_CAR ON DBO.CAR(MAKE, MODEL, [YEAR])
GO

Running the above code, you'll see the following errors:

fig 2

Boy, does that look ugly. The DROP failed because the index is tied to the constraint and it must match the constraint's definition. To change the uniqueness in our example, we can first drop then re-add constraint:

ALTER TABLE DBO.CAR
DROP CONSTRAINT UQ_CAR
GO
ALTER TABLE DBO.CAR
ADD CONSTRAINT UQ_CAR UNIQUE (MAKE, MODEL, [YEAR])
GO

Running SP_HELP CAR again shows that the change has been made:

fig 3


I just noticed I don't have a clustered index on this table. I'd like to make the unique constraint the clustered index since all our reports will sort by make, model, and year. While you can again DROP and CREATE the constraint making it UNIQUE CLUSTERED, you can actually use CREATE INDEX using the DROP_EXISTING option as well. Since the constraint's constraint keys aren't changing, the SQL Server engine will allow you to issue the following statement to change the UNIQUE constraint to clustered:
 
CREATE UNIQUE CLUSTERED INDEX UQ_CAR ON DBO.CAR(MAKE, MODEL, [YEAR])
WITH DROP_EXISTING
GO


Running SP_HELP CAR one more time shows that the constraint's index has been changed to a clustered index:

fig 4
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, February 13, 2018 - 2:06:38 PM - Tim Back To Top (75207)

 Would you recommend doing both to the same column? A unique constraint in the table definition and also a unique nonclustered index on the same column? Is this good, bad, not needed/doesn't matter?

 

Thursday, March 1, 2012 - 8:06:41 AM - Jonas Back To Top (16229)

What if you want to rebuild the indexes on some tables but not others. Is the index that SQL Server by default builds reachable from a alter index rebuild clause?

Is a PK always a clustered one?

The unique constraint that is not a PK is often not clustered because there is already a PK that is.

Thursday, April 15, 2010 - 11:28:28 AM - aprato Back To Top (5248)

look at it from a logical design standpoint irrespective of the index that's built (which may or may not be used).   If you
have a candidate key on the table why wouldn't you define a UNIQUE constraint if, for anything, to document the fact
that the key must be unique within the table?

Thursday, April 15, 2010 - 11:18:04 AM - Repriser2010 Back To Top (5247)

Small table does not need an index, it won't help optimizer. The question is on a large table on non-PK column, do you have a scenario that business requires unique value but it won't benefit from indexing the column (with the overhead already considered). That is where you nake a decision to use one vs the other.

Tuesday, November 4, 2008 - 5:14:54 PM - dQmQ Back To Top (2145)

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.  


Tuesday, November 4, 2008 - 4:06:41 PM - bkshilo Back To Top (2144)

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?

Sunday, September 7, 2008 - 11:30:17 PM - dQmQ Back To Top (1764)

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.

 

  


Sunday, September 7, 2008 - 8:52:42 AM - Preethi Back To Top (1757)

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.


Saturday, September 6, 2008 - 9:03:46 PM - Alex Back To Top (1754)

 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).

Saturday, September 6, 2008 - 7:43:51 PM - aprato Back To Top (1752)

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.


Saturday, September 6, 2008 - 6:49:15 PM - Alex Back To Top (1751)

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.


Saturday, September 6, 2008 - 5:38:38 PM - aprato Back To Top (1750)

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.



Saturday, September 6, 2008 - 1:27:38 PM - dportas Back To Top (1747)

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

Friday, September 5, 2008 - 6:38:13 PM - Alex Back To Top (1745)

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















get free sql tips
agree to terms