Solved

key / index?

Posted on 1998-07-22
2
196 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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