Unique Key Versus Index on sql server 2008 when defining a column to be unique

Question

I have a table named countries and i define the country_name column to be unique by creating a “Index/Key” of type “Unique Key” on sql servwer 2008 r2. but i have the folloiwng question:-

1. will creating “Index/Key” of type “Unique Key” ; automatically creats a non-clustered index on this column?
2. if i change the type from being “Unique Key” to "Index" and i keep the Is Unique value to be "Yes",, then will there be any differences ?

Answer 1

Unique constraint is the element of the logical design. Unique index is the element of the physical design. Internally both of them are using unique index for the implementation. The choice what to use is yours. I personally prefer to use indexes because I can add included columns there if needed which gives me extra flexibility during performance tuning. But again, it's more or less the question about personal preferences

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 2

Unique constraint is the element of the logical design. Unique index is the element of the physical design.

thanks for the reply ,, but what do u mean by this ?

Br

Answer 3

I'm pretty sure Louis would be able to explain it much better than I am. :)

When you convert business requirements into the working system, as the first step you're working with the logical model. You are dealing with entities and attributes, relations between entities, keys, etc. That model is kind of database (or data storage) independent.

Physical database design is done based on the logical model and goes to the object levels (tables, indexes, etc). On that stage you start to take database server implementation details into consideration. Physical database schema does not necessarily exactly maps logical schema  - for example, you can decide to store logical entity in the multiple tables, denormalize the schema and so on.

In real life, of course, logical and physical design stages often mixed with each other. That approach has own pros and cons which is subject of completely different conversation.

What I tried to say - there is no difference in SQL Server physical implementation between unique constraint and unique index. SQL Server uses unique nonclustered index internally to force uniqueness of unique constraint. Unique constraint/key much closer conceptually to the logical design while unique index is purely physical design concept. While forcing uniqueness via unique constraint is cleaner from the logical model standpoint, using unique index instead adds some flexibility for the future performance tuning.

Again, it's up to you what to choose - just a matter of personal preferences. And, of course, you can always start with unique constraint and later replace it with unique index if needed.

---

Thank you!

My blog: http://aboutsqlserver.com