Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following:
- A primary key
- A unique index
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
It has nothing to do with performance as such, but it is a conceptual issue.
Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an index which happens to be unique, for instance because you add the primary key to it.
As per the Scenario:
Both can be either clustered or non-clustered. When you create a constraint you are allowed to choose which type of index you want to use for these keys.
I'm not sure your question has enough information to give a good answer. Whether the PK/UI is Clustered or Non-Clustered is often the dominant performance factor when the resultant join includes fields that were not part of the indexes.
There can be only one clustered index on a table at a time. By default, the PK is clustered. However if a clustered index existed before the PK was created then the PK will be nonclustered (unusual).
So in most cases, since a UI would be created after a PK, the UI would be non-clustered and thus slower when the join results needs to include non-index fields from the row. Comparably, if a clustered UI is created instead of a PK then the performance is expected to be equivalent to a clustered PK.
It is also possible to create indexes with "included" columns. This gets around the clustering issue because it allows the join result to use the included columns without having to physically fetch the row (since the row is in a different location when non-clustered).