Calculated Fields

When you use the FORMULA attribute for a column in EM does SQL Server
actually store the "calculated" data or just materialize it when it's
fetched ?
TIA
LizLiz wrote:
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
The value is not permanently stored unlesss you create an index on the
computed column. For that reason computed columns in tables are of
little value unless you intend to index them.
If you try to update a computed column directly you will get an error.
That's why for most purposes I think it is safer and makes the meaning
clearer if you put computations into views rather than tables. Views
can be indexed too, so I would only consider a computed column where an
indexed view or some other method couldn't achieve the same purpose.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1166266554.326117.237240@.16g2000cwy.googlegroups.com...
> Liz wrote:
>> When you use the FORMULA attribute for a column in EM does SQL Server
>> actually store the "calculated" data or just materialize it when it's
>> fetched ?
>> TIA
>> Liz
> The value is not permanently stored unlesss you create an index on the
> computed column. For that reason computed columns in tables are of
> little value unless you intend to index them.
I tried it out because I'm working with a table that has incredibly bizarre
column names so it just makes it easier to reference a+b+c as MYFIELD ... I
did index the calculated column and then had a brief glitch; a row which
should have matched did not ... then the problem "went away" ... which makes
me uneasy. If the "underlying" columns are indexed does the "calculated
column" effectively "use" the index ? I guess I can look at an execution
plan and find out ...
Thanks,
Liz
> If you try to update a computed column directly you will get an error.
> That's why for most purposes I think it is safer and makes the meaning
> clearer if you put computations into views rather than tables. Views
> can be indexed too, so I would only consider a computed column where an
> indexed view or some other method couldn't achieve the same purpose.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Calculated fields are 'calculated' upon request. The data is not
materialized. (There is a way, however, involving indexing... But that is
not 'normal'.)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Liz" <liz@.tiredofspam.com> wrote in message
news:OeszSGNIHHA.2632@.TK2MSFTNGP06.phx.gbl...
> When you use the FORMULA attribute for a column in EM does SQL Server
> actually store the "calculated" data or just materialize it when it's
> fetched ?
> TIA
> Liz
>