Schema - Do you use computed columns rather than denormalized fields?

<introEmbed body={<> 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. <imageEmbed alt="Image" size="large" showBorder={false} figureEmbed={{ preset: "badExample", figure: 'Bad Example - This field was manually updated from code in the middle tier.', shouldDisplay: true }} src="/uploads/rules/use-computed-columns-rather-than-denormalized-fields/NormalizedFields_Bad.jpg" /> <imageEmbed alt="Image" size="large" showBorder={false} figureEmbed={{ preset: "goodExample", figure: 'Good Example - There was no code in the middle tier to calculate this (and it has the correct name)', shouldDisplay: true }} src="/uploads/rules/use-computed-columns-rather-than-denormalized-fields/NormalizedFields_Good.jpg" /> </>} />

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