SQL Clone
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1666 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

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

Group: General Forum Members
Points: 3740 Visits: 2903
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.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: 1266 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1799 Visits: 1488
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.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2662 Visits: 2204
Thanks for the question!
tilew-948340
tilew-948340
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10827 Visits: 11967
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
Right there with Babe
Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)

Group: General Forum Members
Points: 762 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

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

I run on tuttopodismo
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