Access 2010 Observation

[Me]

I'm bugged.

I just finished watching a video where Ryan McMinn, "Program Manager for the
Access Team" at Microsoft demonstrated lots of new features Access2010 has
for web/sharepoint use. The first demo he did showed how easy it is to add
"calculated columns" to a table. Hello? I was under the impression that
"calculated columns" shouldn't be stored in a table--yet, there he is,
showing how easy it is to come up with a new field for "days in-between"
start date and end date fields.

So, I guess now it's "okay" to store calculated fields within a table? What
happened to queries which would figure this out without storing the end
result???

me

 

[Dirk Goldgar]

I'm bugged.

I just finished watching a video where Ryan McMinn, "Program Manager for
the Access Team" at Microsoft demonstrated lots of new features Access2010
has for web/sharepoint use. The first demo he did showed how easy it is
to add "calculated columns" to a table. Hello? I was under the
impression that "calculated columns" shouldn't be stored in a table--yet,
there he is, showing how easy it is to come up with a new field for "days
in-between" start date and end date fields.

So, I guess now it's "okay" to store calculated fields within a table?

I'm not at all keen on it myself, though the negatives are ameliorated by
the fact that the new calculated columns are computed and, supposedly,
guaranteed by the database engine. So it's not going to happen that a user
just forgets to update the calculation after modifying one of its base
values, which is the most crucial weakness of storing calculated data.

Since I haven't played around with A2010 yet, it isn't clear to me whether
the calculated values are actually stored, which might make them more
efficient to retrieve in some situations than calculating them on the fly,
or whether they are calculated only when the record is retrieved and the
field is accessed, which would seem to have no advantage.

I also don't know yet whether, as in SQL Server, you can create indexes on
the calculated columns. That could be useful, sacrificing purity for
efficiency.

My current plan is to not to use calculated columns, until/unless I run into
some performance issue that can be solved by them. Maybe when I know more
about them I will change my policy.

 

[david]

MS is trying to differentiate between SQL Server
and MSAccess, by taking MS Access down market
to compete with other, more limited products that
are easier to use.

Arguably this is better than their previous strategy
of differentiating between SQL Server and MS Access
by upgrading SQL Server and letting Access stagnate.

It may seem hard to believe now, but Access was
both a cutting-edge relational database managment
system and a cutting-edge rapid application development
system when it was first introduced, and when both of
those markets were of much wider interest than they
are today.

Now that those markets are no longer strategically
important, MS is moving Access to block competition
from Filemaker and Open Office. A clean relational
database model has no point in this move.

(david)

 

[Banana]

So, I guess now it's "okay" to store calculated fields within a table?
What happened to queries which would figure this out without storing the
end result???

me

Well, before you complain about how Access has calculated fields now,
you should remember that the "big boys" such as Oracle, SQL Server, DB/2
has had those features for long time, for better or worse. I'd sooner
complain to those big boys as they're ones that set the trend in the
"arms race".

Next, there has been cases made for wanting calculated fields such as
better performance, ability to index on calculated values, etc., etc.
and that's why several RDBMS, and now including Access has those though
nobody would disagree that this is not fully relational. At this point,
the questions of whether Access' calculated fields are beneficial in
terms of performance and indexing is an open one and hopefully will
become more clear as we understand what it is capable of.