Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Computed Column Divide by Zero? Expand / Collapse
Author
Message
Posted Thursday, October 14, 2010 3:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #1004199
Posted Thursday, October 14, 2010 7:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
Good question with some subtlies around the nature of Arithabort and persisted. Thanks.
Post #1004335
Posted Thursday, October 14, 2010 7:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,914, Visits: 2,525
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
Post #1004340
Posted Thursday, October 14, 2010 11:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:46 PM
Points: 1,142, Visits: 2,684
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
Post #1004671
Posted Thursday, October 14, 2010 2:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,219, Visits: 1,430
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.
Post #1004801
Posted Thursday, October 14, 2010 3:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
Thanks for the question!
Post #1004826
Posted Thursday, October 14, 2010 8:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:37 PM
Points: 589, Visits: 2,437
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!
Post #1004869
Posted Friday, October 15, 2010 12:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 6,007, Visits: 8,269
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
Post #1004924
Posted Friday, October 15, 2010 12:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1004929
Posted Friday, October 15, 2010 1:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:53 AM
Points: 2,529, Visits: 2,402
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.
Post #1004933
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse