[thelist] SQL Indexes

[Scott Dexter]

> 1. Clustered.

one advantage:
if you have a one to many table (like a receipts table --multiple entries
for a specific userid) or a multiple column primary key, index on the
multiple entry field first in the list, with the other fields after it. This
lets the db engine cluster the records together, so the work is almost
already done for you.

> Don't use clustered indexes on columns that are frequently 
> updated, as the
> index will be recalculated each time.

CAVEAT:

MS SQL Server 6.5 does *NOT* update these indexes automatically. Check your
db documentation to see if yours does. There is a setting called the fill %
that dictates how much free space you want in each cluster in the index, the
more inserting you do, the lower you want this because when the cluster that
the insert goes is full, the db sticks it at the end of the data. This
causes 'hot spots,' and can really slow down the performance of the index,
so much so that eventually the query optimizer will ignore it and choose a
table scan instead. You have to rebuild the index to clean up those hot
spots (schedule a stored procedure to run at intervals appropriate for the
fill % you've set and such). SQL Server 7 has the option to automagically
update the indexes set by default (you can turn it off, because it can
affect performance; the index is tweaked on *every* insert).


sgd
--
think safely