Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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