i have one product table. in my table there is a fields like productid , price , quantity , total .
than how i multyply price , and quantity fields and the answer stored in the total field.
2 Answers
Don't store derived values in your tables - this is bad design.
Since total is the product of price and quantity, you can always calculate it when you have these values - why store it? You are creating the potential for inconsistent data in your database if you do so.
Retrieving such a value directly from SQL is trivial, without the need to store it:
SELECT price, quantity, price * quantity AS total
FROM product
Update:
As @Martin notes, SQL Server 2005+ has Computed Columns which are non persisted or indexed columns that as a convenience return the results of a calculation.
I stress that these are columns that are not persisted.
3 Comments
Martin Smith
Entirely depends on what the OP's queries are. If they want the
TOP 10 products ordered by total a computed column would be of benefit here. computed column's pretty much negate all of your arguments against anyway as they ensure there is no potential for inconsistent data and they don't have to be persisted.
Oded
@Martin - so you agree that a
total column shouldn't be persisted.Martin Smith
I didn't say that at all. It depends on what the OP's queries are. If they want to run queries such as "find all products with
total > 10000" then indexing it could be justified.You can create a computed column for this
ALTER TABLE Products ADD total AS price * quantity
With the above definition the column is neither persisted nor indexed so it is just a convenience for your queries.
You might consider indexing such a computed column if your queries needed to use total in a search predicate. Any such persisted values are maintained automatically by SQL Server thus avoiding potential issues with data anomalies.
Additionally in the case that you are doing such queries then even without indexing such columns your queries can benefit from improved cardinality estimates due to statistics on the computed column.