Solved

key / index?

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
Powershell v3 - SQLCMD 3 26
backup and restore 21 29
Get Next number from Stored Procedure 8 22
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…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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