I've got a backend SQL Server database. It's my understanding that clustered indexes are most efficient, but that they physically order pages to make it efficient. So, if it's a user-entered field, every time a new record is added the whole table needs to restructure itself.
This can't be efficient. So, is putting a regular index on a field efficient in this instance? or would you just leave it unindexed?
As a general rule, you should only really use clustered indexes for Identity fields, and autonumbers are the best, since they are ever increasing. Updating an identity column is somthing you should do about once every millenia, and certianly not at application runtime, so you should never have to reorder page contents via a standard DML statement.
Within these constraints clustered indexes are very very efficient, but as you point out,if used incorrectly they can be a major drag on performance for DML statements on the indexed field.
So short answer, do not use clustered indexes for user entered feilds.
As for non-clustered indexes, they can be a major boon, or a caveat, depending on their usage. NCIs are very good are retrieveing one or a small number of rows, but the size of the index grows with each field added to it, so complicated many-field indexes, or indexes that could only resolve a query to a large number of rows at least don't provide the gains, and are often more of a drag than they are worth.
