I want to use some computed columns like

if (MyDate is not NULL then Extract(year from Mydate) else NULL)

That works in the same way as in a generated (always) column. Just to be
sure: what is the difference between a generated and a computed column
in this case?

Uli

Uli


My understanding is that with generated columns the data is physically stored with computed columns it isn't but is computed each time the column is displayed.

I haven't tried it but I assume that when MyDate is altered the generated column is re-generated.

Roy Lambert [Team Elevate]

Roy,

> I haven't tried it but I assume that when MyDate is altered the generated column is re-generated.

Thanks, Roy. So a generated column in this case should use less
resources? In my case it's changed only once anyway. Thus it doesn't
make sense to compute it each time again.

Uli

Uli

>Thanks, Roy. So a generated column in this case should use less
>resources?

That seems probable, but only Tim knows (or anyone who's bought the source and is brave enough to enter its hidden depths)

>In my case it's changed only once anyway. Thus it doesn't
>make sense to compute it each time again.

I have only one computed column so far - its for the text filter in full text indexing so I don't have to store it.

I just tested and a generated field is updated if a base field is changed - I assume Tim has the equivalent of an UPDATE trigger in there.

Roy Lambert [Team Elevate]

Roy,

> I just tested and a generated field is updated if a base field is changed - I assume Tim has the equivalent of an UPDATE trigger in there.

Thanks for the information.

Uli

Uli,

<< That works in the same way as in a generated (always) column. Just to be
sure: what is the difference between a generated and a computed column in
this case? >>

Computed columns are "live", in the sense that they are re-computed directly
as the columns that they reference are changed.  Generated columns are only
re-computed during an INSERT or UPDATE operation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Uli,

<< Thanks, Roy. So a generated column in this case should use less
resources? In my case it's changed only once anyway. Thus it doesn't
make sense to compute it each time again. >>

If the column doesn't change often, then a generated column is a better
solution.  Plus, generated columns can reference user-defined functions,
which computed columns cannot do.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Computed columns are "live", in the sense that they are re-computed
> directly as the columns that they reference are changed.  Generated
> columns are only re-computed during an INSERT or UPDATE operation.

Thank you for the clarification!

Uli