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)