select query for sql server

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 stor开发者_StackOverflow社区ed in the total field.

---

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.

---

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.