key / index?

Posted on 1998-07-22
Medium Priority
Last Modified: 2010-03-19
Can someone explain the difference between a key and
an index in SQL Server? How are they related?
Question by:vd

Accepted Solution

mayhew earned 200 total points
ID: 1089171
A key is a field or combination of fields that are unique.  For example a social security number field might be a good choice as a key.  Something that you know is going to be unique in every row of your table.

An index is used to speed sorts or searches on your table/query based on a field or combination of fields.  For example, you might want to have an index on a last name field, or a zip code field.

An index is basically a list of rowid's (and/or maybe other info depending on the database engine) sorted by the field(s) that is indexed.  For example, if your table looks like:

   NAME  <other columns>
1 john               <other data>
2 bertha
3 frederick
4 ed

and you create an index on NAME, your index might look something like:




depending on the database.

You usually don't have access to what the index actually looks like.  It's more of a behind the scenes thing.  As you can see, though, whenever you add/change/delete rows, you increase your overhead because the index has to be maintained.  (See warning below)

When you set up a key, most databases (to my knowledge) will automatically set up an index on the key field(s).  So if you have a key field, it should be indexed.  If you create your own index, it's usually a secondary index on other fields you want to sort by.

Summation:  A key field(s) is to insure uniqueness.  An index is to help speed searches and sorts.

A word of warning:  there is often a tendency to "over-index" a table.  Indexes on fields that you will be searching on are a good idea.  But an index on a boolean field, for example, will not really help.  To the opposite, it will increase overhead and become a detriment.

Also, I mentioned that a key could be a social security number and that is true.  But it is considered better form to not actually store data in a key field if possible.  For this reason, incrementing integer fields are often used as key fields.

Hope this is helpful!

Author Comment

ID: 1089172
This is an excellent answer. Thankyou.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question