Home
Schema - Do you use computed columns rather than denormalized fields?
  v6.0 Posted at 9/01/2020 8:06 PM by Calum Simpson

When you have a denormalized field, use a computed column.  In SQL Server ​they can be persisted.

Use the suffix "Computed" to clearly distinguish that this field is a computed field.

NormalizedFields_Bad.jpg style=

Figure: Bad Example - This field was manually​ updated from code in the middle tier.
NormalizedFields_Good.jpg
Figure: Good Example​ - There was no code in the middle tier to calculate this (and it has the correct name)

Computed columns have some limitations - they cannot access fields in other tables, or other computed fields in the current table.

You can use user-defined functions (UDF) from code in a reusable function, this allows one computed column to use a function to call another function.  Here is an example:​

ALTER FUNCTION [dbo].[udfEmpTime_TimeTotalComputed]

(
@TimeStart as DateTime,
@TimeEnd as DateTime
)
RETURNS DECIMAL(8,6)
AS
BEGIN
-- This function returns the time difference in hours - decimal(8,6)
​RETURN (round(isnull(CONVERT([decimal](8,6),@TimeEnd - @TimeStart,(0))*(24),(0)),(2)))

END​

Figure: This is the user defined function
NormalizedFieldsDefine.jpg
Figure: Sett​ing up a computed column in the table designer​


Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: