Understanding the way SQL Server 2000 stores, modifies, and retrieves data helps you to design databases for optimal performance.
Which index strategy to apply has a big impact on the overall performance of the database. Different uses require different indexing strategies.
Decision Support Systems (DSSs) are based on reading operations, which read many rows to produce a report, usually with data aggregation. Online Transaction Processing Systems (OLTP) are based in fast access to a small number of rows, producing modifications on the data, forcing index maintenance.
Many systems are a mixture of DSS and OLTP operations. SQL Profiler can help us to determine the actual workload, and the Index Tuning Wizard can suggest an efficient strategy ...