This is another myth that sometimes I observ on many database implementations
(here I'm referring expecially on Navision SQL Server databases): to speed
up queries, sometimes many developers create lots of separate indexes (I've
seen cases where there was an index for every column of the table).
It's true that many SQL Server indexes can permit to improve the select
performances, but they have also a big counterside.
On SQL Server you can have one clustered index (all the rows in the
table are stored in the order of the clustered index key) and many
non-clustered indexes (used for queries and stored as B-tree
structures).
Having multiple indexes can help you to speed up your queries because the
query optimizer has many choice to take when performing the query, but the
counterside that you can have is that:
- indexes consume disk space, so having many indexes results on using more
disk space
- (more important): all your data modification operations (INSERT,
UPDATE, DELETE) can become slow because SQL Server, every time that there's a
changement on the table data, updates all the table indexes.
So? It's extremely important to choose the right index to build when
designing your database, expecially on environment (such as a Navision DB) where
you can have very heavy loads (and lots of concurrent users). If you have table
that are accessed really often only for queries, you can place lots of right
indexes (the indexes useful for query the data), but if you have table that are
updatet frequently, be careful on how many index you are
using...