I totally agree with the other comments: good question. I wanted to add a comment about the effect of persisting the computed column (by adding the PERSISTED option or by including it in an index), but others have beaten me to it. 😉
tommyh (10/13/2010)
why does SQL let something get stored (and im not talking about the computed column itself since that aint stored... unless its persisted) in the database in the first case, that it later cant read? Anyone know the reasoning behind that? (Besides performance that is)
The developer chose not to persist the computed column. That means that the developer required SQL Server not to evaluate the expression until referenced. The only way for SQL Server to "know" that the expression can't be evaluated is by trying to evaulate it, so there is no way to tell when the data is stored if there will be an error - except if SQL Server chooses to disregard the developer's wish.
Further, it might also be the case that the data stored is intermediate. The first stage of processing an order might involve copying the data from the order form; a later stage adds other data. A column that is only needed after the second stage could be a computed column that results in an error before the second stage is completed. Think about a computed column that lists the time between receipt of an order and shipping of the goods - while not producing an error, the computation would not result in a meaningful value if querie while the order is still being processed.
henrik staun poulsen (10/13/2010)
In order to get round the divide by zero error, I generally do this:PF1 AS QF1 / nullif((QF1 + QF2),0),
instead of
PF1 AS QF1 / (QF1 + QF2),
I.e. set the divisor to null, if the divisor is zero.
I used to do complicated Case statements, but then I needed to retype the divisor.
This way, I just add "NULLIF(" at the front and ",0)" at the end.
That would be my suggestion as well. While the suggestion in the explanation works as well, it requires the person who queries the data to know that the column is computed, AND to know the exact expression. Writing the expression so that there can never be an error encapsulates this knowledge and reduces the risk of errors when new people start writing queries.
BTW, the NULLIF function is simply a shorthand for the complicated CASE expressions you used to do. The NULLIF reduces the amount of code to maintain, but does not offer any performance benefits.