Do i need indexing table

Question

I use SQL server 2008 R2. Do I need to to index my table that already has a primary key?

The primary key, the table is indexed automaticaly.

Answer 1

Imagine a telephone guide for all the citizens of the country. Suppose that the primary key is the national id number (US and UK may prefer to think in a social security number).

This kind of table will be fast to check the telephone of a known citizen. The table is sorted by id number so I open the guide by half. I know if the number is in the first half or the other. I keep doing the same until I find the phone. So the search operation cost is logaritmic.

Now imagine that the police want to investigate one unknown telefophone number. The table is sorted by people Ids, not by phone numbers. They will need to look ¡The whole guide! Even in todays computers reading from hard disk is slow. A big table can have MBytes or even GBytes. If the police do this very frecuently the smart way to do is to have all phoner numbers in another list sorted by numbers. Of course this requires updating the index table with every new record and requires some hard disk.

The bigger the table the bigger the difference between logaritmic and linear. So if you have a 1000 records table it is nice to index it. But a badly indexed 1.000.000 record table can destroy your perfomance. The more you read the more you want to index. the more you insert, delete or update the indexed fields the less you want to index it.

Update: The columns that might need to be indexed are those used in:

1. WHERE
2. JOINs
3. GROUP BY

It can become complicated to decide which columns to index. The order might be important. Nulls and functions can make your live harder. The database sometimes tries hacks like using and index with two columns wven when you search only from one. Some fields might have very little information (they are very little selective). This could include sex columns (male / female) that only reduce the info to half.