title: Keep invoice sum right without losing performance?
body: <p>I have two tables...</p>
  
  <pre><code>CREATE TABLE [dbo].[Invoice](
      [InvoiceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [TotalSumBeforeTax] [decimal](12, 2) NOT NULL,
      [TotalSumAfterTax] [decimal](12, 2) NOT NULL
  )
  
  CREATE TABLE [dbo].[InvoiceItem](
      [InvoiceItemID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [InvoiceID] [uniqueidentifier] NOT NULL,
      [AmountBeforeTax] [decimal](12, 2) NOT NULL,
      [AmountAfterTax] [decimal](12, 2) NOT NULL
  ) 
  </code></pre>
  
  <p>The Invoice table holds a calculated sum of all the <code>InvoiceItems</code> that refer to that Invoice.</p>
  
  <p>I need help figuring out the best practice to keep the Invoice sum correct without losing performance.</p>
  
  <p><strong>Calculated column?</strong></p>
  
  <p>If I use a calculated column for <code>TotalSumBeforeTax</code> and <code>TotalSumAfterTax</code> in the Invoice table, that fetches values via a function from the <code>InvoiceItem</code> table, it's not persisted and will be very slow, since I have hundreds of thousands of rows in the <code>InvoiceItem</code> table.</p>
  
  <p><strong>Trigger?</strong></p>
  
  <p>I also thought about using a trigger on the <code>InvoiceItem</code> table and then updating the Invioce <code>TotalSumBeforeTax</code> <code>TotalAfterBeforeTax</code> columns.</p>
  
  <p><strong>What to do?</strong></p>
  
  <p>Is there a way to always keeping the <code>TotalSumBeforeTax</code> and <code>TotalSumAfterTax</code> columns up-to-date with minimal impact on performance when listing many rows of the Invoice table?</p>