For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

Question

Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns?

Like if I have this table:

    CREATE TABLE [dbo].[tbl_shipping_boxes]
    (
        [boxid]     [int] IDENTITY(1,1) NOT NULL,
        [costperten][numeric](10,2),
        [weight]    [int] NOT NULL,
        [length]    [numeric](10,2) NOT NULL,
        [width]     [numeric](10,2) NOT NULL,
        [height]    [numeric](10,2) NOT NULL,
        [volume]    [numeric](10,2),
        [dimwgt]    [numeric](10,2),
        [costperone][numeric](10,2)
    )

I would like to calculate the volume, dim weight, and cost per one. Which would offer me better performance? More reads on this table than writes.

Is one preferred over the other for constant reads, and low writes? What about if I add a quantity field that I would decrement or increment to? Does that change the model I should have?

I am not having luck with persisted columns not recompiling on each query even when marked persisted and using deterministic values and functions. I used database tuning wizard, and it is faster, but I am concerned about the space this will eventually take up.

Thanks

Answer 1

I would not bother with persisted columns at all. I would just define a computed column and have it recalculated every time we select, so that I save considerable storage and have my selects do less reads, at the expense of the negligible amount of CPU needed to calculate volume.

Of course, I did some benchmarks in the past.