More on Computed Columns

Dilip shares features about Computed column that make them most useful. He includes times when using Computed Columns are helpful, as well as cautions for situations that may occur if you do use them. Dilip writes:

Computed Columns have been in vogue for many years and are really helpful.

Here, are some examples/points in this context:

1. You store a mathematical formula in the table. This ensures that the results will be consistently right or consistently wrong, all through out the system.
2. I remember a case where results produced by programs written by two different programmers and who were doing computation of taxable amount in their programs were totally different:
   1. 1st programmer did – 1 x 3 and then divided it by 3 : the result was 1.
   2. 2nd programmer did – 1 / 3 and the multiplied by 3 : the result was 0.99999999.
3. It helps in storing critical data columns as mathematically encrypted form due to the formula in the table. And Since, we never give rights to users to access tables directly, it adds a new layer of security. This is very much true for examination marks stored as computed columns.
4. Computed columns sometimes become problematic when you are switching over from one version of database to another and from one vendor to another as the implementations may be different.
5. Computed columns provide one more advantage as the change in formula does not affect the functioning of remaining system.

Finally, it is up to you to make best use of it. We have always found them to be useful.

I used to use Computed columns more often in the past. Today I tend to put the computations in the domain classes so that the same formulas may be enforced regardless of the data store where you persist your data. Not every application has, or must have, a domain layer. In those cases when a domain layer doesn’t make sense, then it may be a good time to consider placing them in the database.

Do you agree with the comments here today? If not, you may freely share your concerns here online, or simplyl drop an email to btaylor@sswug.org to be included in a future editorial.

Cheers,

Ben