SQL Server › Normalization

When would it make sense to intentionally denormalize a database design, and what specific tradeoffs come with that decision?

Advanced

Answer

You might intentionally denormalize by duplicating some data to significantly speed up frequently run, performance critical read queries, particularly in reporting or data warehouse scenarios, accepting the tradeoff that the duplicated data now needs to be carefully kept in sync, and that write operations become more complex since they may need to update the same information in multiple places.

-- Denormalized for reporting speed: order total pre-calculated and stored
CREATE TABLE Orders (
  OrderId INT PRIMARY KEY,
  CustomerId INT,
  TotalAmount DECIMAL(10,2) -- calculated and stored, not recalculated on every read
);

Real-world example A reporting heavy e-commerce platform intentionally stores a pre-calculated order total directly on the orders table, denormalizing slightly to avoid recalculating that total from individual line items on every single report execution.

Common follow-ups: How do you keep denormalized data properly synchronized as the underlying source data changes?;What is the difference between denormalizing for performance and simply having a poorly designed schema?

Query Optimization & Plans;Data Types & Schema Design