Schema - Do you avoid de-normalized fields with computed columns?
8/11/2019 7:38 AM by
We should always use computed columns (in SQL Server 2005 and later they can be persisted) to avoid these types of denormalized columns.
- Figure: Bad Example
- 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]
(Figure: This is the user defined function
@TimeStart as DateTime,
@TimeEnd as DateTime
-- This function returns the time difference in hours - decimal(8,6)
RETURN (round(isnull(CONVERT([decimal](8,6),@TimeEnd - @TimeStart,(0))*(24),(0)),(2)))
- Figure: Setting up computed column in table designer
Do you feel this rule needs an update?