I need to implement user risk scoring in model

  1. The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, the new risk scoring value needs to be be able to reflect immediately

  2. It will span across 10+ different tables

  3. The query with user list need to be able to filter by RiskScore


  1. I can create a user defined function [udf].[CalculateRiskScore] and make a computed column in [dbo].[User] or query out during view The advantage with this is good maintainability but bad performance.

  2. Another way is to have a trigger for those 10 tables to call [udf].[CalculateRiskScore] and update [RiskScore] into [dbo].[tblUser] with indexing on [RiskScore]. The advantage with this is good performance but poor maintainability since now i have to repeat x times depending on how many table that will affect [RiskScore].

There may be another solution I am not aware of. Is there any way that I can get the best of both worlds while adhering to the requirement?

My objective is to call [udf].[CalculateRiskScore] in one single place only while not sacrificing performance of filtering user with [RiskScore]

  • 1
    Is there a reason that the logic in the function has to be encapsulated in a UDF? Sep 6, 2019 at 12:06
  • 1
    Another option is to create a materialized view (indexed views in SQL Server) based on a view that returns the risk score paired with the user primary key.
    – Brian
    Sep 6, 2019 at 12:59
  • @ErikDarling to make it easier to maintain. I couldn't think of other way of easier maintanence other than encapsulating in udf.
    – c448548
    Sep 6, 2019 at 13:03
  • 1
    If it's a computed column definition, it's already encapsulated. Changing the column definition, or changing the UDF definition if it's referenced in a computed column is the same thing. Also, look at this Q&A: Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?. Sep 6, 2019 at 13:05


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.