Calculated fields...

[Matthew Wells]

If I have a calculated field that returns a value from a udf, does that
field get calculated even when it's not being used in a Select statement? I
knw that if a calculated field's formula is based on fields in the same
record that the calculation is only performed when one of the two fields is
updated. But if I'm calling a UDF, there's no way for SQL Server to know
when to update the calculation. (In this case, the UDF does a count of
records in a child table based on criteria - not an aggregate) Of course if
the calculated field is in a select statement, it will be refreshed. But
what if I'm not calling that field in a query? Is it recalculated? The
bottom line is: How much overhead is there when I use a calculated field to
call a UDF?

Thanks.

--
Matthew.Wells
Ma***********@FirstByte.net

[Plamen Ratchev]

Computed columns are virtual columns that are not physically stored in
the table (they get recalculated when you query the table), unless the
column is marked PERSISTED. The expression defining the computed column
must be deterministic when PERSISTED is specified.

There is an overhead associated with recalculating the computed value,
and it depends on how complex is the calculation. Persisting a computed
column is useful if you will be querying on that column a lot and use it
as filter, then you can index the computed column.

--
Plamen Ratchev
http://www.SQLStudio.com

[Matthew Wells]

That much I got, but does the computed column get computed if it's not part
of a SELECT statement?

"Plamen Ratchev" <Pl****@SQLStudio.comwrote in message
news:Hb******************************@speakeasy.ne t...

Computed columns are virtual columns that are not physically stored in the
table (they get recalculated when you query the table), unless the column
is marked PERSISTED. The expression defining the computed column must be
deterministic when PERSISTED is specified.

There is an overhead associated with recalculating the computed value, and
it depends on how complex is the calculation. Persisting a computed column
is useful if you will be querying on that column a lot and use it as
filter, then you can index the computed column.

--
Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

No, it will not be computed until you query the column. It is very much
like defining a column based on calculation in a view.

--
Plamen Ratchev
http://www.SQLStudio.com