Discussion Locked

indexing a table in sql7

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
What are the advantages of indexing a table in sql7 ? How can it help retrieving data fast if a search query is fired from the front end ?

3 replies

by shanghai sam

I wish this wasn’t such a common question. If the database is under minimal strain then indexing is just a good database administration habit. If, on the other hand, your server is getting used heavily and things are running unacceptably slow,then indexing can definitely help reduce the load.

I will only stress that the database has to have been designed well and normalized properly for this to be completely effective. That being said.

SQL server 7 has two primary ways of retrieving data: table scans or use of indexes.

A table scan is a bad as it sounds. SQL server starts at the first row in the table and searches every row in the table. If it finds a match it adds it to the result set and then continues. Because there is no particular order in a table, it has to read every row to be sure it finds everything. If we issues a SELECT * FROM Names WHERE Name LIKE ‘M%’ against an un-indexed table, you can see that SQL Server has to read every row to find Mikeand Mark.

Un-indexed table called Names
Name:
Scott
Mike
Aaron
Mark
Lee

Now if an index is brought in the game, SQL server can cut down the number of rows it needs to look at. The placement of the index is important and can start to get complicated. In our simple example, if we put an index on the Name column, we will end up with a table that is stored like this.

Indexed table called Names
Name:
Aaron
Lee
Mark
Mike
Scott

If we issue the same query against the indexed table, SQL server can jump to the first record with a ‘M’ and then read them until the ‘M’s are done. It can return the result set without reading the rest of the table because the index told it there are no more rows with a namethat starts with ‘M’.

((((((((((continued in comment below.))))))))))

by shanghai sam

(((((((continue from above)))))))))))
It comes down to a simple phone book analogy. If the phone book was not in alphabetical order you would have to read every single page to find all the names that start with ‘M’. Fortunately, the phone book is indexed on the person’s Last name. So, we can jump straight to the M’s and be certain that when we get to the ‘N’s, we have seen all of the ‘M’s.

Now, there are some more factors about how indexes work. Things like, if you only request data that is included in the index, then the query never even gets to the table, it gets the information right out of the index. This is true in clustered indexes. This is extremely fast, and needs to be considered on your busy tables. Order of index use, order of query statements and many other things come into play.

To sum it up, proper indexing is a sign of administrator that understates a databases operation. We all know it’s easy to make databases, but it is hard to make databases properly. Put the time in up front to design your database, indexes, backups, triggers, etc…and it will pay off.

This discussion is closed to new replies.