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
a) create table CartInfo with columns and fill data on each item change (trigger)
b) create view CartInfo with columns
Both solution are probably valid, but I am not sure based on what facts should I choose either a table or a view?
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..