Formulas for Fields in SQL Server

How much memory?

[woop01]

Does anybody here have experience with using formulas for certain fields in a database that are based on two other fields within that database? How much server power does it take up (min, medium, a lot)? Does it slow down queries to the table?

We've been updating this column using a stored procedure that runs once per hour but if forumals don't take up much memory or slow down reading of the table, we would prefer to use them.

Thanks,

[Dreamquick]

As far as I remember you can't set the default contents of a field using a formula which references other fields from that table, however you might be able to do use one of the following;

View based on the source table, incorporating the formulas as pseudo-columns. Since this would recalculate the formulas for each request it would work best for queries which return small sets of results otherwise you might be looking at an increased load, the optimal size would be determined by the complexity of the formulas you want to use. Would also work well if the formulas change frequently.

Trigger tied to the insert / update events, so whenever a row is added or updated the values will be recalculated. Since this only occurs once per row it's suited to applications involving large amounts of relatively static data - this assumes that the formulas don't change that frequently.

Modify the data-capture system to calculate the formulas. All the benefits of a trigger but with the advantage of not having to worry about additional load because any data-load which involves bulk data will produce a fair amount of load in the first place!

Since you haven't said how much data you are working with or how much you plan to change or how complex the formulas are it's hard to say which is best...

- Tony

[woop01]

About 200,000 rows, almost all of which will change slightly at least once per day.

[Dreamquick]

Hmm... in that case your batch update is probably an efficient solution if you use all the data at once, however if you only use it in small blocks at any one time I'd have a look at the viability of views...

- Tony