Why calculated fields?

Question

Why calculated fields?

bill.sugden

Old Hand

Points: 349
More actions
May 20, 2010 at 7:28 am

#221790

This may be a bit out of scope as a pure design question but I've been having some thoughts as I design a new database that has constructs that can built as calculated fields.
Do we actually need to pollute our nicely normalised structure with calculated fields that use other fields in the database as their arguments, the vast majority of uses I've seen for calculated fields.
If we retain the purity of the main tables then presentation (i.e. accessing program) requirements for calculated fields can be provided by views, this is particularly advantageous when 'entities' are spread across multiple tables.
So are calculated fields in main tables advantageous in other ways? Is there a performance advantage, and remember not all of us are building million hit per minute data schemes.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i most often add a persisted calculated field for indexing purposes.

I've used it to pull out a substring from a field which is often searched, as well as dates with no time portion into calculated fields so i can index them and increase performance; making SARG-able columns and indexes to avoid table scans.

Generally, anything you put in a calculated field could be done in a view instead, so there's no real need from the data presentation perspective to denormalize the table, I agree.

it's the indexing/performance perspective that i tend to use them for in my case, which is a little different objective.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

If the calculation is really simple, say adding two columns, then no, storing it doesn't really make sense, but if the calculation is complicated, then it makes more sense to store the calculation result rather than hope that each piece of code written to access the system gets the calculation right.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

bill.sugden Old Hand Points: 349 More actions · Answer 3

Thanks Lowell

My thoughts almost exactly. I too would use a calculated field if it was required for an Index, but 90% of the time a bit of further analysis can get around the need for this approach (multi-column indexes etc.).

In a complex and huge database I can see that there are efficiency gains using a calculated field but most of the ones that I, and I suspect most developers in SME's, construct would be considered 'noddy' by the big guys, so purity of structure over raw efficiency becomes more important. Using a calculated field for most purposes smacks of the old saw 'Normalize until it hurts, denormalize until it works'