Schema - Do you use computed columns rather than denormalized fields?
Rules To Better SQL Databases - Developers|4e2c5421-72b9-46c2-b5c7-311882ec35fd
v6.0
Posted at
9/01/2020 8:06 PM by
Calum Simpson
Rule Intro
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.

- Figure: Bad Example - This field was manually updated from code in the middle tier.
-
- Figure: Good Example - There was no code in the middle tier to calculate this (and it has the correct name)
Page Content
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

- Figure: Setting up a computed column in the table designer
{6A71C411-854A-4425-A4E8-392E717BEDEC}
Do you feel this rule needs an update?