0

I have a table in my SQL database. It has already some data and I have to add a new column. The value of the column should be created by a SQL function I'd definded. The parameters are two values from the table.

When I SELECT the data it's to late to call the function.

So I have value1 and value2 in my table. And I have a third column which should calculated like this function(value1, value2).

I only found solutions which execute the function at the SELECT.

Can anyone help me how to do this?

Presi
  • 806
  • 10
  • 26
  • [Computed column](https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017) – Dale K Sep 27 '19 at 09:44
  • u can call the function to update the value of new column – JimmyN Sep 27 '19 at 09:46
  • I tried to create a computed column, but i seems like the function is called at select time. So I can't use it. And the problem at update time is, that the data is updated by a datasync which I cannot modify. – Presi Sep 27 '19 at 09:59
  • Please explain why at select time is too late? Given thats the only way you can access data, surely that is exactly the time you need it? – Dale K Sep 27 '19 at 10:03
  • Because I use Power BI. Then the it's extremly slow (about 5 mins for 35k rows). So I want the correct value in the database so I only have to load them. And when I call the function in PowerBi it's as slow as with the computed column. – Presi Sep 27 '19 at 10:20
  • Duplicate of https://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql – GandRalph Sep 27 '19 at 10:52

1 Answers1

0

Use a computed column:

alter t add function_column as (function(value1, value2));

This will be calculated when it is accessed -- which means every time you use it. If you want the value calculated only once, then you would need to add a new column and a trigger to keep it up-to-date.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786