Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Computed Column Divide by Zero?


Computed Column Divide by Zero?

Author
Message
cengland0
cengland0
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1300
Nils Gustav Stråbø (10/14/2010)
Stupid me, lost the points because I was thinking of persisted computed columns. Don't know why.

Same here. As soon as it told me I was wrong, I realized my mistake even before joining the discussion. Oops.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2952 Visits: 2629
Good question with some subtlies around the nature of Arithabort and persisted. Thanks.
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3342 Visits: 2816
Good question today. Learned something which makes it worthwhile

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Trey Staker
Trey Staker
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 2788
Good question, Thanks! Also I don't know why I didn't know about nullif() so I learned a bit more from the comments.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Tom Brown
Tom  Brown
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 1466
Thanks to all of you who took the time to comment on my question.

I've learnt something too from your suggestions: I'll be using the nullif tip and looking into persisted columns.

The Arithabort part was added because I imagined Hugo critiquing my question, - and as I have been caught out by other questions which didn't explicitally state such detail, I wanted to make sure there could be no room for ambiguity.

Incidentally if anyone is toying with the idea of submitting a question for QOTD - imagine Hugo reading it before you submit, it will sharpen your thinking. ;-)
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the question!
tilew-948340
tilew-948340
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 Visits: 2437
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!
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11548
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
Hugo Kornelis (10/15/2010)
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.




Hi Hugo which is the best pratice for Computed columns having PERSISTED or without having PERSISTED option or having an index for the computed columns

Thanks & Regards
Deepak.A
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3600 Visits: 3233
Be aware of computed columns that use complex formula or call functions, they may lead to performance problems:

create function getsum(@id int)
returns int
as
begin
return (select sum(id) from sysobjects where id < @id)
end
GO
create table a(id int identity(1,1) primary key,a int,sum_id as dbo.getsum(id))
insert a select id from sysobjects

select * from a -- the function getsum is called for each row to compute sum_id

In this case, I prefer to use a trigger to compute the column.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search