Microosft Database Primary Keys - How Indexes Work in MS Access and SQL Server

Microosft Database Primary Keys - How Indexes Work in MS Access and SQL Server

Our Example

Consider a simple table in Access or SQL Server. It contains 3 columns or fields:

An integer field giving the number of the person in the table (PersonId)

The person's first name (FirstName)

The person's last name (LastName)

Suppose that the first five records are as follows:

1 - David - Cameron

2 - Susan - Shaw

3 - David - Brown

4 - Susan- Cameron

5 - Robert - Smith

Primary Keys

Many times Access or SQL Server will need a way to locate a record in this table as quickly as possible. To do this, we need to be able to give the database the smallest possible bit of information, and it should be able to use this to return a record. For example, if we give the database the number 2, it will return Susan Shaw.

The field which defines uniquely which record to return is called the primary key of the table. In the case above, there are a few candidates:

The first name field (but unfortunately, this falls down because there are two Davids and two Susans)

The last name field (but likewise there are two Camerons)

We could get round the problem above by making the combination of first name and surname unique, but even this will fail if - as seems almost certain - we have two people with the same first name and last name.

The best solution is to create a field which uniquely identifies exactly which record we are considering. Usually this is called an Autonumber field (Access) or identity field (SQL Server), meaning that it starts at 1 and automatically increases by 1 for each new person entered into the database. Thus we make the PersonId field our primary key (by right-clicking on it in Access or SQL Server and choosing Set Primary Key).

Indexes

Suppose that we ask our database to list out all of the people in alphabetical order of surname. The database will need to sort the data by the surname, so that Brown comes before Cameron, which in turn comes before Shaw. For 5 records this will be almost instantaneous, but for 50,000 records it will take longer.

A way to speed this up is to index the LastName field. What this means is that Access (or SQL Server) will keep an internal list of the records in the database sorted by primary key, and another one for each indexed field. For the LastName field this will look like this:

3 - David - Brown

1 - David - Cameron

4 - Susan- Cameron

2 - Susan - Shaw

5 - Robert - Smith

Now whenever we ask to see people sorted by surname, the results will appear immediately, since the sorted data will already be compiled.

The downside of this is that you're asking your computer to maintain two separate lists. Typically you should avoid indexing more than 4 or 5 fields in a table, as after that performance can take a big hit.

And that's it - the mysteries of primary keys and indexes revealed!

POST COMMENT

Data Logger - Data Storage Devices Password recovery tools State Of Art Rehabs Support With Individualized Recovery Treatment Are You Utilizing Your Data Base? Apple Ipad : Free 10 Gb Data Per Month APC Battery Backup System - Easy Way To Prevent Data Loss Advantage of performing Onshore Data entry and Data processing within borders of USA Top Data Recovery Company In San Francisco - Why You Should Know About It Make Disaster Recovery Plans Before it is Too Late Debt Collection and Debt Recovery - How It Works Basic Guidelines for Data Recovery Recovering Unmountable Oracle 9i Database DATA RECOVERY NEEDS AND IMPORTANCE