Store aggregate data in a table or a view?

I am doing a project in MS SQL server and i am currently concerned about the following case.

CartItems (table)
CartID      ItemID      Value
1           51          100
1           52          100

If I want to have a cart sum available as a fixed value and not to be calculated with SUM for each query do I

  • Change SQL Server stored procedure in C#
  • Error at the transport level when sending a request to the server
  • Efficient way to string split using CTE
  • What is a SQL Server module?
  • SQL Server 2008 Trigger
  • Entity Framework Mapping SQL Server tinyint to Int16
  • a) create table CartInfo with columns and fill data on each item change (trigger)

    • CartID
    • CartTotal

    b) create view CartInfo with columns

    • CartID
    • CartTotal

    Both solution are probably valid, but I am not sure based on what facts should I choose either a table or a view?

  • how many datasources can coldfusion handle
  • There is already an object named 'tbltable1' in the database
  • How to Identify port number of SQL server
  • Multiple INSERT statements vs. single INSERT with multiple VALUES
  • SQL Server 2014 Case Sensitivity issue
  • Query: find rows that do not belong to a list of values
  • 3 Solutions collect form web for “Store aggregate data in a table or a view?”

    Consider an indexed view. That would materialize the total, providing the performance benefits of the table approach but without extra code to maintain it. The total will reflect new values in real time as underlying data are changed.

    CREATE VIEW dbo.CartItemSummary
    WITH SCHEMABINDING AS
    AS
    SELECT
          CartID 
        , SUM(Value) AS CartTotal
    FROM dbo.CartItem
    GROUP BY CartID;
    GO
    CREATE UNIQUE CLUSTERED INDEX cdx_CartItemSummary ON dbo.CartItemSummary;
    GO
    

    I’d strongly recommend using a view here, since you don’t have to handle an additional table and trigger which makes your solution less maintainable and more brittle. A view is exactly what you need: A (different) view on your existing data. You then don’t need to be concerned about keeping the data in the second table “synchronized” (e. g. by using a trigger). The results of the view are always “up to date”.

    Views don’t contain information in the way a table does; they’re simply a stored sql that is run every time you select from them. You may be referring to a materialised view, but it’s not a SQL Server offering as far as I’m aware..

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.