Stephen,

>Do you set deleted on?

I've set DELETED to ON in all my applications.

>I lean towards the other direction with respect to indexing. When in doubt, index it. When users start to add off the wall reports later, the criterior that they choose is rarely what I considered in the front end design of the app. It's just a CYA kind of thing.

You might experience performance degrading (especially when replacing, appending and deleting lots of records) when you have lots of indexes. I found that if I only use the indexes I really need, the application performs much better and more secure (index which don't exist, don't go currupt, fragmented or unbalanced).

>Will I determine if the users will delete records or not? I don't think that I can make that call. My users are more like Steve Urkle, "Did I do that?"

Well You don't need an index for every selection you want to make on your data, just the important columns like:

- Primary keys and foreign key's
- other high selective fields (like Date fields).
- other fields neccesary for sorting data.

An index which has a low selectivity (an index value refers to a big portion of the table, like the deleted() index) is not as effective as a high selective index (each indexvalue refers to only one or just a few records)

Like described in the SQL server training documents, when a query concernes a great deal of the entire table, it may be more efficient to use no index at all (Full table scan). IOW the overhead of using indexes to optimize takes the same time (or more) to read the rest portion of the table which is not involved in the query.

Walter,