Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.1.5. Denormalizing Your Database

Denormalizing a database is deliberately adding redundant data to a database to improve performance. Although Online Analytical Processing (OLAP) databases are highly denormalized, it's not uncommon for an Online Transaction Processing (OLTP) database to have some level of denormaliza-tion designed to improve the performance of specific queries.

NOTE

OLTP databases have a high level of transactions (UPDATE, INSERT, and DELETE statements). OLAP databases are highly queried (SELECT statements), but have very little, if any, transactions. Adding redundant data in an OLTP database has a performance cost because any updates to the redundant data must be made in multiple places. Because an OLAP database is rarely updated, the redundant data doesn't add any additional maintenance costs.

For a simple example of denormalization in a database, imagine that you have a Products table that includes the columns shown in Figure 1-7. This table is normalized.

Figure III.1-7. A normalized Products table.

Imagine that management has recently become very interested in the cost of on-hand inventory. On a weekly basis, and often on a daily basis, queries are run that identify the total value of on-hand inventory.

Every row in the Products table must be examined. Multiplying the OnHand inventory column by the ProductCost column calculates the on-hand cost of each ...