Multiple Computed, Dependent Columns on INSERT in SQL Server?

Question

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/Azure environment.

For some context, I've already translated a heavily denormalized reporting table that had hard coded excel formulas (no DAX) into a 3NF RDBMS with one main fact table and all of the applicable dimensions, all with proper primary and foreign key constraints, consistent grain, etc.

The fact table is simply just the heavily normalized set of foreign keys, where applicable, along with the data entry from a user form that will be used to compute the resulting field values.

Here's a super simplified version in a test environment: https://www.mycompiler.io/view/5WbQMoQOOyL

While I can of course render all of the proper denormalized foreign key values in a simple view using JOINS, I find it challenging to know what the best approach would be to render the multiple dependent, cascading calculated or computed columns.

I find a view is too simple and a trigger is (perhaps) too complex. Maybe a stored procedure and or various table value function? I'm honestly not sure.

I'd like to have this information persisted as opposed to computed or calculated each time the data is called in a query or used in a result set for external stakeholders, but I could also be totally off base here and need an entirely different approach. I just want to ensure speed, reliability and accuracy in the processing.

For more context, the total number of records is not very high, roughly 40-50k, adding maybe 9-10k each year, maybe a few hundred give or take each week.

Here's a simplified visual:

Column A -- data entry value by user, captured in fact table
Column B -- data entry value by user, captured in fact table
Column C -- A * B
Column D -- data entry value by user, captured in fact table
Column E -- C * D
Column F -- C - E

Any ideas or suggestions would be greatly appreciated.

Answer 1

For what it's worth, I think you already have the solution you're looking for: using PERSISTED computed columns. As a side note, you might want to ease up on your exchange rate datatype definition a little, as exchange rates can fluctuate wildly. I've gone with ( 18, 6 ) below as that tends to maintain typical FX rate precision in the vast majority of rate interchanges I've worked with.

CREATE TABLE [dbo].[Transactions_Test] (
    [Record ID] INT IDENTITY( 1 ,1 )    PRIMARY KEY,
    [Total Gross Fee Local Currency]    DECIMAL( 18, 2 ),
    [Exchange Rate]                     DECIMAL( 18, 6 ),
    -- [Total Gross Fee Local Currency] * [Exchange Rate]
    [Total Gross Fee USD]               AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) ) PERSISTED,
    [Account Fee Share %]               DECIMAL( 6, 5 ),
    -- [Total Gross Fee USD] * [Account Fee Share %]
    [Account Fee Share $]               AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
        * [Account Fee Share %] ) PERSISTED,
    -- [Total Gross Fee USD] - [Account Fee Share $]
    [Adjusted Gross Fee USD]            AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
        * [Account Fee Share %] ) ) PERSISTED,
    [Dept Fee Share %]                  DECIMAL( 6, 5 ),
    -- [Adjusted Gross Fee USD] * [Dept Fee Share %]
    [Dept Fee Share $]                  AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
         * [Dept Fee Share %] ) PERSISTED,
    -- [Adjusted Gross Fee USD] - [Dept Fee Share $]
    [Net Fee USD]                       AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) PERSISTED,
    [AM Fee Share %]                    DECIMAL( 6, 5 ),
    -- [Net Fee USD] * [AM Fee Share %]
    [AM Fee Share $]                    AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) 
        * [AM Fee Share %] ),
    -- [Net Fee USD] - [AM Fee Share $]
    [Total Net Fee USD]                 AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
                * [Dept Fee Share %] ) )
        - ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) 
        * [AM Fee Share %] ) 
);

INSERT  INTO dbo.Transactions_Test ( [Total Gross Fee Local Currency], 
    [Exchange Rate], [Account Fee Share %], [Dept Fee Share %], [AM Fee Share %] )
VALUES  ( 10, 122.12, 0.12, 0.30, 0.25 );

SELECT  [Record ID], [Total Gross Fee Local Currency], [Exchange Rate], 
        [Total Gross Fee USD], [Account Fee Share %], [Account Fee Share $], 
        [Adjusted Gross Fee USD], [Dept Fee Share %], [Dept Fee Share $],
        [Net Fee USD], [AM Fee Share %], [AM Fee Share $], [Total Net Fee USD]
FROM    dbo.Transactions_Test;

DROP TABLE [dbo].[Transactions_Test];