CELKO (3/9/2015)
I'm going to go out on a limb here and suggest that, since calculating a product price probably requires no data updates, that should be done using an in-line Table Valued Function if possible.
I would make that suggestion into a look-up table
https://www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql-/
People still think of doing computations in SQL when it is a database language. But it stinks for computations! A million row table is nothing these days.
Look up tables are great as far as they go. The Student's T-test example you gave in that article is highly appropriate, and in many cases when a direct lookup is all that's required it's going to be a lot faster.
There are cases however where that really isn't practical. T-SQL may not be suitable for calculations, but it is perfectly capable of doing them. I have had to write several instances where complex calculations were implemented in a T-SQL function and it whooped seriously on any attempts to do the same in C#.Net. In those cases, it was because the calculation usually involved quite a few table rows, possibly from different tables that achieved an initial lookup with some calculational gymnastics added to complete the algorithm.
The trick is more along the lines of correctly determining where it is best to do something.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St