Home
Schema - Do you avoid de-normalized fields with computed columns?
  v1.0 Posted at 8/11/2019 7:38 AM by Tiago Araujo

We should always use computed columns (in SQL Server 2005 and later they can be persisted) to avoid these types of denormalized columns.​

NormalizedFields_Bad.jpg style=
Figure: Bad Example
NormalizedFields_Good.jpg
Figure: Good Example​

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

We use user defined functions (UDF) to encapsulate our logic in reusable functions, this allows one computed column to use a function to call another function.

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

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 computed column in 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: