What's the best way to implement a computed column?
I am using entity framework 6 and the first database approach to build a project,
Let's assume these 2 tables:
Tasks
id | taskName | taskPrice
1 | Making UI| 100
2 | Debugging| 70
and the second table:
Employees
id | Name | spentHours| taskId | *totalPrice*
1 | Iman | 10 | 2 | (10 * 70) = 700
2 | Sam | 5 | 1 | (5 * 100) = 500
I need to make the totalPrice value available for use across all sections of my project, so it's best not to calculate it for every use.
To achieve this, I came up with these three solutions: 1- I could highlight the totalPrice column and update its value, which in my opinion is not the most optimized way, because if the taskPrice changes I would need to recalculate and update the totalPrice values.
The second method is to use a stored procedure.
3-, and the third is with a trigger.
Which method is the most optimized in terms of computation, overhead and time?
If there is any other way to do this, please let me know.
source to share
You can have a computed column in DDL. CREATE TABLE tablname (id int, name varchar (20) spent hours int, taskid int, fn_totalprice (spent hours, id) as totalprcie)
The function can be created function fn_totalprice hours worked int, id int IN LINE (SELECT (e.spenthours * t.taskprice) as totalprice From employees e internal tasks of the union t to e. @Id = t.id return totalprice)
source to share