Unique Key or Index with 'Is Unique'

I'm having a rather silly problem. I'll simplify the situation: I have a table in SQL Server 2008 R2 where I have a field 'ID' (int, PK) and a Name (nvarchar(50)) and Description (text) field. The values in the Name - field should be Unique. When searching the table, the Name - field will be used so performance is key here.

I have been looking for 2 hours on the internet to completely understand the differences between Unique Key, Primary Key, Unique Index and so on, but it doesn't help me solve my problem abo开发者_高级运维ut what key/constraint/index I should use.

I'm altering the tables in SQL Server Management Studio. My question for altering that Name - field is: should I use "Type = Index" with "Is Unique = Yes" or use "Type = Unique Key"?

Thanks in advance!

Kind regards, Abbas

A unique key and a primary key are both logical constraints. They are both backed up by a unique index. Columns that participate in a primary key are not allowed to be NULL-able.

From the point of view of creating a Foreign Key the unique index is what is important so all three options will work.

Constraint based indexes have additional metadata stored that regular indexes don't (e.g. create_date in sys.objects). Creating a non constraint based unique index can allow greater flexibility in that it allows you to define included columns in the index definition for example (I think there might be a few other things also).

A unique key cannot have the same value as any other row of a column in a table. A primary key is the column field(s) that is a unique key and not null which is used as the main look up mechanism (meaning every table should have a primary key as either a column or combination of columns that represent a unique entry).

I haven't really used indexes much, but I believe it follows the same logic.

See http://en.wikipedia.org/wiki/Unique_key for more information.

An index is a collection the DBMS uses to organize your table data efficiently. Usually you want to create an index on columns and groups of columns that you frequently search on. For example, if you have a column 'name' and you are searching your table where name = '?' and index on that column will create separate storage that orders that table so searching for a record by name is fast. Typically primary keys are automatically indexed.

Of course the above is a bit too general and you should consider profiling queries before and after adding an index to ensure it's being used and speeding things up. There are quiet a few subtleties to indexes that make the application specific. They take extra storage and time to build and maintain so you always want to be judicious about adding them.

Hope this helps.