tilew-948340 (10/14/2010)
Hardy21 (10/13/2010)
Good question.If computed columns are persisted in nature (e.g. QTot AS QF1 + QF2 PERSISTED,) then you receive the error after "Insert Zeroes" print statement and "SELECT * FROM @Quantities" statement returns 2 rows - datasource Test1 & Test3.
I am a little confused here: I tried the query with PERSISTED, making sure that I set ARITHABORT and ANSI_Warning to off and I got this message: "DECLARE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations." wich I don't get if the "PERSISTED" is not there (having the three row, 0 for the division by zero).
So, how come the "PERSISTED" could arrange things? Do I forget to do something?
And yes, this is a nice question because I learned a lot. Thanks!
If you want to try it with PERSISTED, then just add PERSISTED at the end of the computed column declarations and change nothing else.
The reason PERSISTED changes the results is because PERSISTED changes the behaviour from the default "don't evaluate on insert and update, but evaluate when queried" to "evaluate on insert and update and store the result; don't evaluate when queried but return stored result". So that means that errors that occur during evaluation change from query time to insert/update time.