Databases and indexing

I wrote this at home about a year ago, and sent it to one of our DBAs (the one who's good for questions and writes bad code, not the one who writes good code and responds to most of my questions with "Let me know when you find out.") for verification. He said I had it basically right:

Each table has one, and only one, efficient way to look up data. This may be the actual order of the table, but it is more likely a B-Tree sort of data structure. For a named table (see below for what I mean about that), this is called the clustered index and, if a table does not have a clustered index, this lookup capability is wasted.

An index is a table which does not have a name of its own, and is automatically kept in synch with a named table. Like any other table, it has one, and only one, efficient way to look up data; this efficient lookup will be by the indexed fields from the original table. The index table contains the indexed fields, plus the primary key of the original table (or possibly a surrogate, if the PK is something hideous). Queries against the original table, using the appropriate fields, will be redirected to the index table. If all the fields used in the query are in the index, the entire query is run against the index table, which is called a covering index; otherwise, a join back to the original table will be performed.

In SQL 2000 Professional, an Indexed View is the same as an index in implementation, but the opposite in access method. Like an index, it is a table which is automatically kept in synch with another table. However, this table has a name of its own, and may only be accessed using that name.

In SQL 2000 Enterprise and Developer Edition, an Indexed View is a combination of an index and a SQL 2000 Pro Indexed View. It is a table which is automatically kept in synch with another table. It has a name of its own, and may be accessed either through that name or through the name of the original table.

Practical Implications:
1. In SQL 2000 Enterprise, there is no performance downside to using an indexed view, rather than an index.
2. If a table has a non-clustered primary key, and has no other indexes, an unneccesary index was created.
2. If a table has a clustered primary key, all the other indexes should perform faster when they are not acting as covering indexes.