3

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.