Newsletter Tip: Triggers for Summary Data

The last sql-server-performance.com newsletter came with this tip:

quote:If your application needs to retrieve summary data often, but you
don't want to have the overhead of calculating it on the fly
every time it is needed, consider using a trigger that updates
summary values after each transaction into a summary table.

While the trigger has some overhead, overall, it may be less that
having to calculate the data every time the summary data is
needed. You may have to experiment to see which methods is
fastest for your environment. [6.5, 7.0, 2000]

IMHO, Indexed Views would also seem ideal for this kind of situation. This will work the same way as indicated above and you don't have to define any triggers. It is also possible to reap further benefits from the indexed view if the index key is used frequently in retrieving your summary data (instead of futher indexing your trigger-generated summary table).

Any thoughts on this?

Nathan H.O.
Moderator
SQL-Server-Performance.com

This is a good idea. I wonder which method offers the better performance? Without testing, I wouldn't want to guess.

-----------------------------
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com