Computed Column Divide by Zero?

Question

Computed Column Divide by Zero?

Tom257

SSChampion

Points: 11543
More actions
October 13, 2010 at 9:50 pm

#225029

Comments posted to this topic are about the item Computed Column Divide by Zero?
October 14, 2010 at 2:02 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

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.

Thanks

deepak.a SSCertifiable Points: 5330 More actions · Answer 2

Thanks For the Good Question Tom Brown i learnt some thing new today and also i tried to create index on the computed columns it will not alllow to create index directly.

and i tried in this way

PRINT 'Define Table';

CREATE TABLE #Quantities (

ID INT IDENTITY,

DataSource varchar(30) NOT NULL,

QF1 Float,

QF2 Float,

QTot AS QF1 + QF2,

PF1 AS QF1 / (QF1 + QF2),

PF2 AS QF2 / (QF1 + QF2) );

CREATE NONCLUSTERED INDEX IXC1 on #Quantities(ID) INCLUDE(PF1,PF2)

PRINT 'Insert data';

INSERT INTO #Quantities (DataSource, QF1, QF2)

SELECT 'Test 1' , 66, 34;

Print 'Insert Zeroes';

INSERT INTO #Quantities (DataSource, QF1, QF2)

SELECT 'Test 2', 0, 0;

Print 'Insert Nulls';

INSERT INTO #Quantities (DataSource, QF1, QF2)

SELECT 'Test 3', null, null;

PRINT 'Show Table';

SELECT * FROM #Quantities;

DROP TABLE #Quantities

i got the error while inserting the value it self

thanks again for the Good question

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

Lost two points because of misunderstanding of set arthiabort on.I already worked divide by zero errors and handle these type of errors.but little bit confused.with arthabort you can handle this other wise using nullif also we can handle.Againg thanks for good question on basics.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

tommyh SSCertifiable Points: 6302 More actions · Answer 4

Great question.

However... 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)

/T

Henrik Staun Poulsen SSCertifiable Points: 6578 More actions · Answer 5

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.

I'we written about it at Stack overflow

Best regards,

Henrik Staun Poulsen

http://www.stovi.com

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 6

Stupid me, lost the points because I was thinking of persisted computed columns. Don't know why.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 8

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),

Good. The computed column should be NULL, if expression is invalid.

cengland0 SSCertifiable Points: 6102 More actions · Answer 9

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 SSC-Dedicated Points: 34566 More actions · Answer 10

Good question with some subtlies around the nature of Arithabort and persisted. Thanks.

sjimmo SSChampion Points: 11139 More actions · Answer 11

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 SSCarpal Tunnel Points: 4796 More actions · Answer 12

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

Tom257 SSChampion Points: 11543 More actions · Answer 13

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. 😉