"Danny" <da*****@mindspring.com> wrote in message
news:35**************************@posting.google.c om...
sorry for posting a question that has probably been posted hundreds of
times, but i can't seem to find the answer i need...We're using a Sql
Server 7.0 database and recently started getting timeout expired
errors on our views, which i'm assuming is due to the volume of
records in our tables. Unfortunately i'm not too experiences w/
indexing tables, so my question is:
1) how do you determine which fields to index (does it have to be a
field w/ no nulls, no duplicates..similar to primary key), and why not
index the primary key
2) is it better to index the same field on different tables, or is it
a better design to index a different field for each table (ex. do you
want to index the same field that you link the different tables with,
or do you want to avoid doing this)
3) can somebody explain the concept of creating a clustered index
4) once table is indexed, if there are users accessing the linked
tables via Access, do they have to re-link the tables?
sorry for the number of questions and what may appear to be simple
questions to most, but again, i'm pretty unfamiliar w/ indexing and
would really appreciate any help...thanks
Indexing is big topic, and critical for performance, so I suggest you start
looking into the Books Online documentation on indexes and performance
tuning. You can find a lot of information on the web, too. One quick way to
get going would be to use the Index Tuning Wizard to recommend where you
should place indexes.
As for your questions above, here are some brief answers (which contain
plenty of over-simplification and over-generalization):
1. Look at the columns in the WHERE clauses of your queries. You can index
any column (except bit or computed columns in SQL7), or a combination of
columns. Adding more indexes to a table generally makes SELECTs faster, but
INSERT/UPDATEs slower.
2. Index the appropriate columns on each table - columns used in joins are
almost always worth indexing.
3. In a clustered index, the table data is sorted in the order of the index
(the index in fact holds the data), so you can only have one per table (and
most tables should have one). As a general rule, queries returning single
rows don't benefit much from a clustered index; queries returning a range of
rows, or an aggregate of a number of rows will benefit. Note that primary
keys are clustered by default, but this may not be the best choice.
4. Indexing is completely invisible to clients - they have no idea what
indexes, if any, are on the table. The exception would be if you create a
UNIQUE index, and the client performs an INSERT or UPDATE which would
violate the uniqueness. In that case, the client will get an error.
Simon
