Is a COMPUTED column only computed when its needed?

I'm writing a skills and competency system and there will be multiple levels of guidance on how to score attainment / training suggestions. I'm adding my end user query generator and was thinking about having the option to query all text for a word/words. However, this will be a major overhead it the row value is computed for every row move> This would be acceptable for a query but not for simple table navigation especially when the computed columns won't be shown.


Roy Lambert

On 7/10/2016 9:28 AM, Roy Lambert wrote:
> Is a COMPUTED column only computed when its needed?
>
> I'm writing a skills and competency system and there will be multiple levels of guidance on how to score attainment / training suggestions. I'm adding my end user query generator and was thinking about having the option to query all text for a word/words. However, this will be a major overhead it the row value is computed for every row move> This would be acceptable for a query but not for simple table navigation especially when the computed columns won't be shown.

Roy,

I'm far from an expert on computed columns but here is goes anyways.

Computed columns AFAIk are "virtual" and computed on the client side so
they would need to be computed on demand every time you read that row -
so if you navigate to a row then computed column for that row would need
to get computed and if you edit the row it could get re-computed again.

There likely are some optimizations Tim has done underneath but worst
case scenario is one where computed column relies on something that is
unique every time so no caching is possible : for example CURRENT_GUID
as part of computation. Every row navigation in this case would require
re-compute and return a new GUID.


Raul

On 7/10/2016 1:09 PM, Raul wrote:
> There likely are some optimizations Tim has done underneath but worst
> case scenario is one where computed column relies on something that is
> unique every time so no caching is possible : for example CURRENT_GUID
> as part of computation. Every row navigation in this case would require
> re-compute and return a new GUID.

And after re-reading i realized i did not actually answer you question
which is something only Tim would really know (since navigating to row
and referring to column will for sure result in re-compute so it's hard
to check unless you dive into source).

However i would hope that it gets computed on-demand (meaning when
you're actually retrieving column value ).

Raul

Raul


>However i would hope that it gets computed on-demand (meaning when
>you're actually retrieving column value ).

That's my hope as well, but as you say without the source we need the "fount of all wsdom"

Roy

Roy,

I use Computed Columns extensively, I don't actually know how they are managed memory-wise but find no overhead with them what-so-ever, however they are managed.

I regularly include them in queries, in search processes etc. and they seem lightning fast.

Adam

Roy,

<< Is a COMPUTED column only computed when its needed? >>

Yes, a computed column is only re-computed when a) a column that it depends upon is changed, and b) the computed column itself is referenced in some way.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Is a COMPUTED column only computed when its needed? >>
>
>Yes, a computed column is only re-computed when a) a column that it depends upon is changed, and b) the computed column itself is referenced in some way.

Super - i went down that route hoping that was the case.

Roy