• tilew-948340 (10/15/2010)


    Hi Hugo.

    The first time I tried the PERSISTED, I got the divide by zero error AFTER the "show table" wich does not seem to be, as I understand it, what it should be doing. So I tried many "set" option, wich brought me to the "declare" error...

    So, if I put the PERSISTED word, should not the error happend after the "Insert null"?

    Here is the code that I used - if you compare this to the QotD, you'll see that adding PERSISTED at one place is the only modification.

    PRINT 'Define Table';

    DECLARE @Quantities TABLE (

    DataSource varchar(30) NOT NULL,

    QF1 Float,

    QF2 Float,

    QTot AS QF1 + QF2,

    PF1 AS QF1 / (QF1 + QF2),

    PF2 AS QF2 / (QF1 + QF2) PERSISTED);

    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;

    If I run this (from SSMS, against SQL Server 2005), I get an error after "Insert Zeroes", and a result set with two rows (one with values; one with NULLs)


    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/