Computed Column Divide by Zero?

  • Comments posted to this topic are about the item Computed Column Divide by Zero?

  • 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

  • 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

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

  • 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

  • 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

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

  • 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/

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

  • This was removed by the editor as SPAM

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

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

  • 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

  • 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

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

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

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