Example SQL Server SQL that uses a formula

I have a table that has a field, call it "score". Is it possible to write an SQL that takes the value in each "score" field, inputs the value in a formula, then the output from the formula is written to another field. I have no experience is using formulas in SQL, and I do not know if its possible. Any basic example, if its doable, would 开发者_JS百科be helpful.

---

Yes it's possible, but almost always you don't want to store the calculated field in the database as that would mean your database is not properly normalized.

Instead calculate the value when you query for it by including an extra derived column in your select statement.

An exception to this is if you need to do a time-consuming calculation, in which case you may want to store the derived value in the database. A good way to do this is to use triggers to update value when one of the inputs change, or else run a procedure on a timed interval to update all the derived values in the entire table.

---

UPDATE OrderLine SET
TotalPrice = UnitPrice * Quantity

That's it.

---

It's called a computed column - they're supported in:

• tables
• SELECT statements
• VIEWs

---

Instead of storing it, I'd advise you to compute it in real-time upon query or as computed column or to use an indexed view if performance is too slow.

---

Pretty vague question, but here's the general idea.

UPDATE MyTable SET FormulaOutput = HomeScore + AwayScore

This "formula" just adds the 2 scores together

I suppose you could also calculate the "spread" like this:

UPDATE MyTable SET Spread = MAX(HomeScore,AwayScore) - MIN(HomeScore,AwayScore)