Denormalization: When, How and Who Keeps It Consistent?

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10).

Guess First: Is Computing the Average Rating Every Time Expensive?

On the category page, each book shows its review count and average rating. In the normalized model neither value lives in any table; both are computed from the reviews table on every request. One day someone says "let's add review_count and avg_rating columns to books instead of computing them every time". Guess: how many times cheaper does this make reading, how much more expensive does it make writing, and is it the first thing to do?

In this post we answer that question by measuring. Denormalization means deliberately storing repeated or derived data in a normalized model. In the previous two posts we saw how to remove repetition; here we discuss when and how to bring it back, and when we do, who keeps the copy consistent.

Measure First: Denormalization Is a Performance Decision

I loaded the Bookstore schema of the SQL Temelleri series into the dm_08 database. The sample data has 181 reviews; to make the difference visible I added 4000 readers and 61,600 reviews with random ratings:

INSERT INTO dbo.customers (first_name, last_name, email, country_code)
SELECT N'Reader', CONCAT(N'No', value), CONCAT('reader', value, '@example.com'), 'TR'
FROM GENERATE_SERIES(1, 4000);

INSERT INTO dbo.reviews (book_id, customer_id, rating, reviewed_at)
SELECT b.book_id, c.customer_id,
       1 + ABS(CHECKSUM(NEWID())) % 5,
       DATEADD(DAY, -((c.customer_id + b.book_id) % 365), '2026-06-30')
FROM dbo.books AS b
CROSS JOIN dbo.customers AS c
WHERE c.email LIKE 'reader%'
  AND (c.customer_id * 7 + b.book_id * 13) % 5 = 0;

SELECT COUNT(*) AS review_count FROM dbo.reviews;

(4000 rows affected)

(61600 rows affected)
review_count
------------
61781

(1 rows affected)

The normalized query for the category page joins books with reviews and groups them. Because the container runs under emulation, I look at logical reads with SET STATISTICS IO instead of timings; I kept only the logical reads part of those output lines:

SET STATISTICS IO ON;
SELECT b.book_id, b.title,
       COUNT(r.review_id) AS review_count,
       CAST(AVG(r.rating * 1.0) AS DECIMAL(3,2)) AS avg_rating
FROM dbo.books AS b
LEFT JOIN dbo.reviews AS r ON r.book_id = b.book_id
WHERE b.category_id = 3
GROUP BY b.book_id, b.title
ORDER BY b.title;
SET STATISTICS IO OFF;

book_id  title                      review_count  avg_rating
-------  -------------------------  ------------  ----------
26       Dune                       805           3.06
27       Dune Messiah               804           2.98
28       Foundation                 804           2.99
34       Hyperion                   801           2.95
29       I, Robot                   805           3.02
33       Neuromancer                806           2.87
30       The Left Hand of Darkness  802           3.06
31       The Time Machine           801           2.90
32       The War of the Worlds      800           3.01

(9 rows affected)
Table 'Worktable'. Scan count 1, logical reads 171
Table 'reviews'. Scan count 1, logical reads 217
Table 'books'. Scan count 1, logical reads 3

The whole reviews table was scanned: 217 pages, plus a 171-page worktable for the grouping. The first question to ask before denormalizing is this: is this query slow because of a bad model, or because of a missing index? Let's add an index on reviews ordered by book_id that also carries the rating, and run the same query (the result rows are identical, so I only show the logical reads lines):

CREATE INDEX IX_reviews_book_id ON dbo.reviews (book_id) INCLUDE (rating);
GO
SET STATISTICS IO ON;
SELECT b.book_id, b.title,
       COUNT(r.review_id) AS review_count,
       CAST(AVG(r.rating * 1.0) AS DECIMAL(3,2)) AS avg_rating
FROM dbo.books AS b
LEFT JOIN dbo.reviews AS r ON r.book_id = b.book_id
WHERE b.category_id = 3
GROUP BY b.book_id, b.title
ORDER BY b.title;
SET STATISTICS IO OFF;
GO
DROP INDEX IX_reviews_book_id ON dbo.reviews;

Table 'reviews'. Scan count 9, logical reads 41
Table 'Worktable'. Scan count 0, logical reads 0
Table 'books'. Scan count 1, logical reads 3

Reads went down from 391 pages to 44, and the schema holds not a single copy. Most "let's denormalize" proposals end here. The details of indexes, worktables and plan operators are in the "Nonclustered Index Tasarımı" post of the SQL Server Derinlemesine series. Denormalization only comes to the table if the read cost is still unacceptable after the right index and reads happen far more often than writes. The book card is a typical candidate: a book is viewed thousands of times a day and gets a few reviews. To decide, you need two numbers: the read/write ratio and the read and write cost of each path. We measure the second for three paths below.

This is also what separates denormalization from design laziness: behind it there is a measurement and a mechanism that keeps the copy up to date. Without both, what you have is not denormalization but the order spreadsheet from the 5th post.

Persisted Computed Columns: Derived from the Same Row

The cheapest denormalization is a value derived from columns of the same row. The line amount (quantity * unit_price) can be computed in every query; but if reports use it constantly, it can be stored as a persisted computed column. The database computes it itself on every INSERT and UPDATE, so the copy never drifts from its source. It has a limit too: the expression can only see the same row. Let's try to store the order total the same way:

ALTER TABLE dbo.order_items ADD
    line_total AS CAST(quantity * unit_price AS DECIMAL(12,2)) PERSISTED;
GO
ALTER TABLE dbo.orders ADD
    total_amount AS (SELECT SUM(quantity * unit_price) FROM dbo.order_items AS oi
                     WHERE oi.order_id = order_id);
GO
SELECT TOP (3) order_id, quantity, unit_price, line_total
FROM dbo.order_items
ORDER BY order_item_id;

Msg 1046, Level 15, State 1, Server b8be943222fa, Line 2
Subqueries are not allowed in this context. Only scalar expressions are allowed.
order_id  quantity  unit_price  line_total
--------  --------  ----------  ----------
1         2         290.90      581.80
1         1         460.90      460.90
2         1         590.00      590.00

(3 rows affected)

line_total was added; total_amount was rejected because it looks at rows in another table. If a value derives from other rows, you have to pick a mechanism to store it. Each section below is one mechanism.

Stored Derived Columns: Application Code in the Same Transaction

The most direct path is to update the copy in the same transaction as the code that changes the source. In an application this is a repository method or the aggregate itself; here I show the same behaviour with a procedure:

ALTER TABLE dbo.orders ADD total_amount DECIMAL(12,2) NOT NULL
    CONSTRAINT DF_orders_total_amount DEFAULT (0);
GO
UPDATE o
SET total_amount = s.total
FROM dbo.orders AS o
JOIN (SELECT order_id, SUM(line_total) AS total
      FROM dbo.order_items GROUP BY order_id) AS s ON s.order_id = o.order_id;
GO
CREATE OR ALTER PROCEDURE dbo.add_order_item
    @order_id INT, @book_id INT, @quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;
        INSERT INTO dbo.order_items (order_id, book_id, quantity, unit_price)
        SELECT @order_id, book_id, @quantity, list_price
        FROM dbo.books WHERE book_id = @book_id;

        UPDATE dbo.orders
        SET total_amount += (SELECT line_total FROM dbo.order_items
                             WHERE order_id = @order_id AND book_id = @book_id)
        WHERE order_id = @order_id;
    COMMIT;
END;

SELECT order_id, total_amount FROM dbo.orders WHERE order_id = 1;
EXEC dbo.add_order_item @order_id = 1, @book_id = 26, @quantity = 2;
SELECT order_id, total_amount FROM dbo.orders WHERE order_id = 1;

order_id  total_amount
--------  ------------
1         1042.70

(1 rows affected)
order_id  total_amount
--------  ------------
1         1823.70

(1 rows affected)

Two copies of Dune were added and the total grew by 781.00. Because the line and the total are written in the same transaction, they either commit or roll back together. The weak point of this path is its scope: the rule only holds for writes that go through this procedure. If a support script updates order_items.quantity directly, the total quietly stays wrong. Keeping a copy in application code depends on the discipline "this table is written only through this path". In DDD (Domain-Driven Design) terms, the order and its lines are a single aggregate and the total is an invariant of that aggregate; the "Aggregate Tasarımı" post of the Domain-Driven Design series covers that boundary.

The Book Card: Stored Columns and a Trigger

Back to the book card. I add the review count and the rating sum to the books table; the average is a computed column because it derives from the same row. Storing the sum instead of the average is a deliberate decision: a sum and a count can be updated incrementally, an average cannot.

ALTER TABLE dbo.books ADD
    review_count INT NOT NULL CONSTRAINT DF_books_review_count DEFAULT (0),
    rating_sum   INT NOT NULL CONSTRAINT DF_books_rating_sum DEFAULT (0);
GO
ALTER TABLE dbo.books ADD
    avg_rating AS CAST(rating_sum * 1.0 / NULLIF(review_count, 0) AS DECIMAL(3,2));

UPDATE b
SET review_count = s.review_count,
    rating_sum   = s.rating_sum
FROM dbo.books AS b
JOIN (SELECT book_id, COUNT(*) AS review_count, SUM(rating) AS rating_sum
      FROM dbo.reviews
      GROUP BY book_id) AS s ON s.book_id = b.book_id;

(77 rows affected)

To keep the copy up to date, this time I put a trigger in the database, next to the source. A trigger runs on every INSERT, UPDATE and DELETE, whatever path they come from; the support script is caught too:

CREATE OR ALTER TRIGGER dbo.trg_reviews_book_stats
ON dbo.reviews
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    WITH delta AS (
        SELECT book_id, COUNT(*) AS cnt, SUM(rating) AS total FROM inserted GROUP BY book_id
        UNION ALL
        SELECT book_id, -COUNT(*), -SUM(rating) FROM deleted GROUP BY book_id
    )
    UPDATE b
    SET review_count += d.cnt,
        rating_sum   += d.total
    FROM dbo.books AS b
    JOIN (SELECT book_id, SUM(cnt) AS cnt, SUM(total) AS total
          FROM delta GROUP BY book_id) AS d ON d.book_id = b.book_id;
END;

SET STATISTICS IO ON;
SELECT book_id, title, review_count, avg_rating
FROM dbo.books
WHERE category_id = 3
ORDER BY title;
SET STATISTICS IO OFF;

book_id  title                      review_count  avg_rating
-------  -------------------------  ------------  ----------
26       Dune                       805           3.06
27       Dune Messiah               804           2.98
28       Foundation                 804           2.99
34       Hyperion                   801           2.95
29       I, Robot                   805           3.02
33       Neuromancer                806           2.87
30       The Left Hand of Darkness  802           3.06
31       The Time Machine           801           2.90
32       The War of the Worlds      800           3.01

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0
Table 'books'. Scan count 1, logical reads 3

Reading costs 3 pages; reviews was not touched at all. The price is on the write side:

SET STATISTICS IO ON;
INSERT INTO dbo.reviews (book_id, customer_id, rating, reviewed_at)
SELECT 26, customer_id, 5, '2026-07-01'
FROM dbo.customers WHERE email = 'ada.reviews@example.com';
SET STATISTICS IO OFF;

SELECT review_count, avg_rating FROM dbo.books WHERE book_id = 26;

Table 'customers'. Scan count 0, logical reads 4
Table 'books'. Scan count 0, logical reads 2
Table 'reviews'. Scan count 0, logical reads 5
Table 'books'. Scan count 0, logical reads 2

(1 rows affected)
review_count  avg_rating
------------  ----------
806           3.07

(1 rows affected)

books appears twice: one is the foreign key check, the other the trigger's UPDATE. Two extra pages per review look cheap, but the real cost is not in the number: every review arriving at the same time for a popular book wants to update the same books row, and that row becomes a hot spot. The hidden costs of triggers, nested execution and the need for set-based writes are in the "Function'lar ve Trigger'lar" post of the SQL Server Derinlemesine series.

Indexed Views: A Summary the Database Maintains

The third path is to define the summary as an indexed view instead of a table. When you put a unique clustered index on a view, SQL Server writes the result to disk and updates it in the same transaction whenever the source table changes; you don't need to write a trigger. The first attempt uses AVG:

CREATE VIEW dbo.book_review_avg
WITH SCHEMABINDING
AS
SELECT book_id, COUNT_BIG(*) AS review_count, AVG(rating) AS avg_rating
FROM dbo.reviews
GROUP BY book_id;
GO
CREATE UNIQUE CLUSTERED INDEX UX_book_review_avg ON dbo.book_review_avg (book_id);

Msg 10125, Level 16, State 1, Server b8be943222fa, Line 1
Cannot create index on view "dm_08.dbo.book_review_avg" because it uses aggregate "AVG". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

The error message tells you the fix as well: AVG cannot be maintained incrementally, SUM and COUNT_BIG can. It is the same decision as in the trigger. Indexed views have other rules too: WITH SCHEMABINDING, two-part table names, COUNT_BIG(*) when there is a GROUP BY, no outer joins and no subqueries. I disable the trigger and create the view:

DROP VIEW dbo.book_review_avg;
DISABLE TRIGGER dbo.trg_reviews_book_stats ON dbo.reviews;
GO
CREATE VIEW dbo.book_review_stats
WITH SCHEMABINDING
AS
SELECT book_id, COUNT_BIG(*) AS review_count, SUM(rating) AS rating_sum
FROM dbo.reviews
GROUP BY book_id;
GO
CREATE UNIQUE CLUSTERED INDEX UX_book_review_stats ON dbo.book_review_stats (book_id);

SET STATISTICS IO ON;
SELECT b.book_id, b.title, s.review_count,
       CAST(s.rating_sum * 1.0 / s.review_count AS DECIMAL(3,2)) AS avg_rating
FROM dbo.books AS b
JOIN dbo.book_review_stats AS s WITH (NOEXPAND) ON s.book_id = b.book_id
WHERE b.category_id = 3
ORDER BY b.title;

INSERT INTO dbo.reviews (book_id, customer_id, rating, reviewed_at)
SELECT 26, customer_id, 4, '2026-07-01'
FROM dbo.customers WHERE email = 'alan.reviews@example.com';
SET STATISTICS IO OFF;

book_id  title                      review_count  avg_rating
-------  -------------------------  ------------  ----------
26       Dune                       806           3.07
27       Dune Messiah               804           2.98
28       Foundation                 804           2.99
34       Hyperion                   801           2.95
29       I, Robot                   805           3.02
33       Neuromancer                806           2.87
30       The Left Hand of Darkness  802           3.06
31       The Time Machine           801           2.90
32       The War of the Worlds      800           3.01

(9 rows affected)
Table 'book_review_stats'. Scan count 0, logical reads 18
Table 'Worktable'. Scan count 0, logical reads 0
Table 'books'. Scan count 1, logical reads 3
Table 'book_review_stats'. Scan count 0, logical reads 6
Table 'Worktable'. Scan count 3, logical reads 12
Table 'customers'. Scan count 0, logical reads 4
Table 'books'. Scan count 0, logical reads 2
Table 'reviews'. Scan count 0, logical reads 5

(1 rows affected)

Reading costs 21 pages (nine seeks into the view, plus books); writing adds 6 pages for the view's index and a 12-page worktable. The NOEXPAND hint asks for the view's index to be used directly; according to Microsoft Learn, the optimizer only uses indexed views automatically in certain editions, and Standard edition needs this hint. In PostgreSQL the counterpart is the materialized view, but it behaves differently: according to the PostgreSQL 18 documentation, its contents are only renewed when REFRESH MATERIALIZED VIEW runs and are not updated automatically when the source table changes. Refreshing without blocking readers requires CONCURRENTLY and a unique index on the view. So PostgreSQL's materialized view is closer to the summary table in the next section.

Summary Tables and Batch Recomputation

For summaries that don't need to be current on every write, the simplest and most robust path is to recompute them periodically. A daily sales report doesn't need minute-level accuracy; a job that runs at night is enough. Recomputation is easier to keep correct than incremental updates: an error from yesterday fixes itself on the next run.

CREATE TABLE dbo.daily_sales (
    sales_date  DATE          NOT NULL,
    order_count INT           NOT NULL,
    units_sold  INT           NOT NULL,
    revenue     DECIMAL(14,2) NOT NULL,
    computed_at DATETIME2(0)  NOT NULL,
    CONSTRAINT PK_daily_sales PRIMARY KEY (sales_date)
);
GO
DECLARE @from DATE = '2026-06-01', @to DATE = '2026-07-01';
BEGIN TRANSACTION;
    DELETE FROM dbo.daily_sales WHERE sales_date >= @from AND sales_date < @to;

    INSERT INTO dbo.daily_sales (sales_date, order_count, units_sold, revenue, computed_at)
    SELECT CAST(o.ordered_at AS DATE), COUNT(DISTINCT o.order_id),
           SUM(oi.quantity), SUM(oi.line_total), SYSUTCDATETIME()
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled'
      AND o.ordered_at >= @from AND o.ordered_at < @to
    GROUP BY CAST(o.ordered_at AS DATE);
COMMIT;

SELECT TOP (3) sales_date, order_count, units_sold, revenue
FROM dbo.daily_sales
ORDER BY revenue DESC;

(0 rows affected)

(11 rows affected)
sales_date  order_count  units_sold  revenue
----------  -----------  ----------  -------
2026-06-06  2            6           2433.20
2026-06-09  1            5           1922.70
2026-06-03  1            2           1540.50

(3 rows affected)

The window (@from, @to) matters: late refunds and cancellations can change the last few days, so each run of the job should recompute not just today but the last few days. The computed_at column tells whoever reads the report how fresh the data is.

Two more techniques belong to the same family. Copied display columns copy a value from another table (author name, category name) onto the row to make listing easier; each needs a synchronization path. A JSON (JavaScript Object Notation) read model prepares the data a screen needs as a single document and updates it with events; the architectural side of this path is in the "Ayrı Read Model: Materialized View, Senkronizasyon ve Eventual Consistency ile Yaşamak" post of the CQRS (Command Query Responsibility Segregation) ve Event Sourcing series.

Who Keeps It Consistent?

Each technique gives a different answer to the same question: when the source changes, who updates the copy, and at what moment?

We already produced one breakage in this post. To measure the indexed view I disabled the trigger and then added a review; a script that disables triggers to speed up a large import does the same thing. A check query that compares the stored count with the real one catches it:

SELECT b.book_id, b.review_count AS stored_count, COUNT(r.review_id) AS actual_count
FROM dbo.books AS b
LEFT JOIN dbo.reviews AS r ON r.book_id = b.book_id
GROUP BY b.book_id, b.review_count
HAVING b.review_count <> COUNT(r.review_id);

book_id  stored_count  actual_count
-------  ------------  ------------
26       806           807

(1 rows affected)

Whichever mechanism you choose, run a check like this as a job and wire the difference to an alert. Every system that keeps copies drifts sooner or later; the question is not whether drift will happen but how long it takes to notice.

In Production

Document the decision with the measurement. A note like "category page went from 391 logical reads to 3, writes grew by 2 pages per review, read/write ratio 2000 to 1" says why the denormalization exists and when it can be removed. Measure with production-like data volumes; a measurement with 181 reviews says nothing.

Watch the hot row. Counters kept by a trigger or by application code increase lock wait times when heavy writes hit the same row. For a popular book's review counter this is rarely a problem; for the stock counter of a ticket sale it is a serious bottleneck. In such cases batch or event-based updates may fit better.

Plan the backfill and the way back. Adding a new copy column means a backfill for existing rows; on large tables do it in chunks. Removing the column is a migration too. The backward-compatible deployment of schema changes is in the "Migration'ların Deploy'daki Yeri" post of the Deployment Stratejileri series.

Write the source of the copy into the schema. Putting "maintained from reviews by trg_reviews_book_stats" in the description of the books.review_count column stops the next developer from updating it by hand.

Common Pitfalls

Mistaking snapshot data for denormalization. order_items.unit_price is not a copy of books.list_price; it is the price at the time of the order, a separate fact. A job that tries to "synchronize" it with the catalog price changes the amounts of past orders. The shipping address, the billing name and the tax rate are of the same kind: write explicitly in the model whether something is a copy or a record of that moment.

A copy column without a synchronization path. Copying title onto order_items to make listing easier and never deciding what happens when the book's title is corrected. Either it is a snapshot and not changing is correct, or it is a copy and there must be a mechanism that updates it. There is no state in between.

Denormalizing without trying an index. In the first measurement above, 89 percent of the read cost went away with a single index. Denormalization starts where the index is not enough.

Storing the average. An average cannot be updated incrementally; to compute the new average from the old one when a value arrives, you need the count anyway. Store the sum and the count, derive the average.

Writing the trigger only for INSERT. Reviews get deleted, ratings get changed. The mechanism that keeps the copy must see every change to the source; that is why the trigger above processes inserted and deleted together.

A Small Experiment

★☆☆ Enable the trigger again (ENABLE TRIGGER) and fix the drift in the books table with a single UPDATE. Then verify that the check query returns nothing.

★★☆ Update a review's rating from 1 to 5 and check that the trigger changes rating_sum correctly. Repeat the same UPDATE by changing the review's book_id: are both books' counters correct?

★★★ Write a procedure for daily_sales that recomputes only the affected day when an order is cancelled. How would you choose the window of the nightly job? Which column is missing from the orders table to answer this question with data?

We measured that denormalization is a deliberate decision and what it costs. In the next post we look at decisions made without thinking: modeling anti-patterns such as EAV (Entity-Attribute-Value), comma-separated lists, phone1/phone2/phone3 columns and the one big lookup table, why they look tempting and which query makes them blow up.