OTOlabs

Last October, I started a series of blog posts over on sqlblog.com that highlighted some bad practices I observe from time to time.  Earlier this year, I felt motivated to pick the series up again.
…

I’m not a big fan of denormalizing, nor of repeating redundant redundant information in a database when those facts can already be derived from other information.  A classic example of the latter is when I see questions on newsgroups, forums or StackOverflow that ask how they can update a table’s rank column to reflect the current rank based on some other criteria in the table.  The problem with this is that you have to run the update every time any DML operation touches any row in the table, and if you can calculate that rank in an UPDATE query, you can also calculate that rank in a SELECT query, so why not just figure it out in real time?  An example of the former is when we want to maintain a second table with aggregates from another table.  This is where I want to spend my time today.

In reality, many of us deal with OLTP systems that must also serve as the reporting source, so it’s not always feasible to calculate aggregates in real time against a constantly moving target.  In several of these scenarios I’ve used indexed views, where we have tables that are inserted often, read often for statistics, but rarely or never updated… sure, you pay a little hit up front on the insert, but the benefit achieved during real-time reporting was worth it.  Let me give you an idea of what I was dealing with initially, and how I have fixed it over time to perform even better.

One of our systems is…

…

…

…

…