The dashboard shouldn't rescan all history to redraw today's totals. Summary tables keep agreed aggregates ready for reading. Their usefulness depends on a refresh rule that catches corrections as well as new rows.
Define the Exact Grain of Summary Tables
A daily summary needs a stable key, such as business date and product. Its measures must have a clear definition. Decide whether Amount includes refunds, taxes, or canceled records before writing the aggregation.
The detail query and summary refresh must use the same rule. A total can be mathematically correct while representing a different business meaning from the dashboard label.
I write the reconciliation query before building the refresh. That keeps the definition testable. Use temporary tables for the demonstration below. Dates and amounts are invented inputs, not observed sales.
The changed-day table represents a durable queue in a real design. Every relevant insert, update, and delete must queue affected business dates. A moved row contributes both its old and new date.
CREATE TABLE #Sales(SaleId int PRIMARY KEY,SaleDate date NOT NULL,Amount decimal(19,4) NOT NULL);
CREATE TABLE #DailySummary(SaleDate date PRIMARY KEY,TotalAmount decimal(38,4) NOT NULL);
CREATE TABLE #ChangedDays(SaleDate date PRIMARY KEY);
INSERT #Sales VALUES (1,'20240101',10),(2,'20240101',20),(3,'20240102',30);
INSERT #ChangedDays VALUES ('20240101'),('20240102');Recompute Changed Days as a Set
Recomputing complete affected days is easier to make idempotent than applying blind deltas. A retry replaces the same day's total from current detail. Include days whose last detail row was deleted.
Those days need a stored zero or a removed summary row under an explicit rule. The example keeps a zero row because the changed-day set drives the aggregation with a LEFT JOIN.
The transaction removes and rebuilds only listed days. SERIALIZABLE makes this small session-local example's read-and-write contract explicit. In a shared production design, coordinate writers, queue acknowledgement, and the transaction carefully.
Don't acknowledge a queue item before its corresponding summary commit. A failed refresh must leave enough recorded work for retry. A green job status must not conceal an unfinished date.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
DELETE d FROM #DailySummary AS d
JOIN #ChangedDays AS c ON c.SaleDate = d.SaleDate;
INSERT #DailySummary(SaleDate,TotalAmount)
SELECT c.SaleDate,COALESCE(SUM(s.Amount),CONVERT(decimal(38,4),0))
FROM #ChangedDays AS c
LEFT JOIN #Sales AS s ON s.SaleDate = c.SaleDate
GROUP BY c.SaleDate;
COMMIT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;Capture Late Arrivals and Corrections
A row arriving today can belong to last month's business date. A load that refreshes only today's date misses it. Queue the row's SaleDate, not merely the ingestion timestamp.
Updates moving a sale to another day affect both dates. Deletes require the old date to survive in change capture or a queue. Once the row disappears, a simple scan cannot discover that missing date on its own.
I check delete handling before accepting an incremental plan. A high-water mark on an update column captures some changes, but it doesn't automatically retain deleted rows. It also needs tie handling and a stable watermark commit.
State the capture mechanism plainly. A queue, supported change tracking design, or reliable source change feed can supply the dates. An optimistic timestamp filter cannot substitute for those guarantees.
Choose Between a Trigger and a Job
A trigger can maintain the summary inside the detail transaction. That gives immediate consistency but adds work and contention to every affected write. It must handle multirow inserted and deleted sets.
A trigger written for one row fails its contract during bulk changes. Keep summary updates set-based and test conflicting writes to the same day before relying on the synchronous design.
An Agent job moves work out of the request path and introduces refresh lag. Expose that lag to the dashboard instead of implying the totals are current at every instant. Keep overlapping refreshes serialized or partition their work safely.
A trigger can also capture changed-day keys while a job performs the expensive aggregation. That separates reliable capture from heavy maintenance without hiding the delay.
Compare Summary Tables With an Indexed View
An indexed view maintains a stored aggregate under specific schema, expression, and SET-option requirements. Aggregated indexed views need COUNT_BIG and supported definitions. They also add work to detail changes.
Compare that maintenance cost with the queue-and-job approach. The view is useful when its supported shape matches the requirement and the write workload can carry synchronous maintenance.
Don't force an unsupported expression into the view by changing the business definition. Late arrivals update the indexed result when they belong to its source. The workload still pays for those writes.
A custom summary table supports more complex transformations and refresh policies. Choose based on correctness requirements and resource costs. The fewest lines of SQL don't necessarily describe the simplest operational design.
Reconcile at the Same Read Point
Compare the stored summary with a fresh detail aggregation. Use a consistent read point so concurrent transactions don't create false differences. The following query includes detail-only and summary-only dates through a FULL JOIN. On the sample data it returns no rows, because each day holds 30 in both places.
It treats an intentional zero-only summary day according to the example's policy. Adjust that policy if absent days need to remain absent instead of appearing as zeros.
Check more than a grand total. Two wrong days can cancel each other in an overall sum. Compare every summary key and every measure.
Include refunds and moved dates in the validation fixture. Which corrected day would your current job forget? That question exposes the capture boundary more effectively than watching another successful run process only new rows.
WITH Detail AS
(
SELECT SaleDate,SUM(Amount) AS TotalAmount FROM #Sales GROUP BY SaleDate
)
SELECT COALESCE(d.SaleDate,s.SaleDate) AS SaleDate,
d.TotalAmount AS DetailTotal,s.TotalAmount AS SummaryTotal
FROM Detail AS d
FULL JOIN #DailySummary AS s ON s.SaleDate = d.SaleDate
WHERE COALESCE(d.TotalAmount,0) <> COALESCE(s.TotalAmount,0)
OR s.SaleDate IS NULL;Publish Summary Tables With Their Freshness
Record the successful refresh time, processed queue boundary, and validation result. A dashboard should distinguish stale totals from current totals and failed loads from zero activity. Keep refresh failures visible and retain queue items for retry.
Summary tables hold derived data. Their value depends on explaining when and how it last matched the authoritative detail under the chosen definition.
Use summary tables when repeated aggregations justify stored results and the team can own their freshness. Keep capture reliable, rebuilds idempotent, and reconciliation detailed. A small table makes reads convenient.
It doesn't make the underlying change stream simpler. Yesterday's correction still wants attention, even when the dashboard has already moved on to today's colorful chart.
Related reading on this blog: Indexed Views: When the Faster Reads Are Worth It and Incremental Loads: Moving Only What Changed.
A stored total is not a permanent fact, it is detail data maintained under a refresh contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
