Indexes for Various Database Application Types
Each database application type can benefit from a different indexing strategy. The reason is that SQL Server has to maintain indexes when data is added or modified. Each time you INSERT, UPDATE, or DELETE records in a table with indexes, SQL Server has to modify values not only in the table, but also in the indexes. Therefore, the index that speeds up your SELECT queries might slow down INSERT, UPDATE, and DELETE queries going against the same table.
Generally, Online Transaction Processing (OLTP) systems benefit from a few indexes, whereas the Decision Support Systems (DSS) perform well with many indexes. Of course, the sad news is that many companies use the same database for data entry and reporting. Data warehousing (DW) applications benefit from a couple of indexes on the dimension tables and many indexes on the fact tables.