locked
How to design summary tables RRS feed

  • Question

  • Hi guys.. how do you design summary tables? and in which part of the day do you fill them up?

    I have a summary table called StockCard it has something to do with inventory and item movements. anyways I'm confused when to fill(update) them up, which is better at the end of the day as batch? or rapid updating per transaction during operating hours? 

    Sunday, September 22, 2013 12:19 PM

Answers

  • I depends on your latency requirements.  If the summary tables are used only for end-of-day reporting, you could update them once a day.  But if the amounts must be transactionally consistent, you'll need to update them in real-time as the underlying data are changed.

    Instead of maintaining separate tables, you might be able to create views with the needed aggregate queries and then create an index on the view.  This would keep the summary information updated in real time.  However, there are a number of restrictions regarding indexed views so that might not be an option.  See http://msdn.microsoft.com/en-us/library/ms191432.aspx


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Martina White Sunday, September 22, 2013 9:08 PM
    • Marked as answer by Allen Li - MSFT Friday, September 27, 2013 7:53 AM
    Sunday, September 22, 2013 3:29 PM
    Answerer