subject: Microosft Database Primary Keys - How Indexes Work in MS Access and SQL Server [print this page] 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!
