The SQL system I admin is for a lab and has only one user (or SSRS automation) querying at a time. Its updated continuously by an application via ODBC.
SO, when creating an index on a table, Don stated that the index gets updated whenever the indexed column is updated
Tell me if I have this right then.
I have ~100 "ItemID" fields, I am collecting a data value every 5 minutes for each of these ItemID's.
At the end of the month I query against only one of these ItemID's selecting all the values that are between a certain range. The result gives me a timestamp for all the values and using SSRS I create a trend and reports for this data.
There are no indexes at this time.
Since I only run this query once a month, even though the query takes several minutes to run, creating an index for ItemID would not be a good idea as every 5 minutes the index would be updated. The Query might be faster but the continuous load on the CPU would not be a fair tradeoff?
I get that each situation is unique and I would need to look at the system performance with the index created and not and look for a difference between the two options, but overall, is that what you were trying to convey?