Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

key / index?

Posted on 1998-07-22
2
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
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:

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

604 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