Solved

key / index?

Posted on 1998-07-22
2
186 Views
Last Modified: 2010-03-19
Can someone explain the difference between a key and
an index in SQL Server? How are they related?
0
Comment
Question by:vd
2 Comments
 
LVL 5

Accepted Solution

by:
mayhew earned 50 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:

2
4
3
1

or

bertha
ed
frederick
john

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!
0
 

Author Comment

by:vd
ID: 1089172
This is an excellent answer. Thankyou.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now