Iterating through every row efficient?

Question

Suppose I have the following table T1:

| col1 | col2 |
|------|------|
| 0    | 0    | // A++
| 3    | 123  | // B++
| 0    | 5    | // C++
| 8    | 432  | // A++
| 0    | 4    | // B++

I now need to create a trigger (on INSERT), that analyses every row, increases a counter (see below), populates the table T2 with the values of the counter:

IF col1 = 0 AND col2 = 0
    A++
ELSE IF col1 = 0 col2 > 0
    B++
ELSE IF col1 > 0
    C++

In this case, T2 would look like:

| id | A | B | C |
|----|---|---|---|
| 1  | 1 | 2 | 2 |

My question is more about the design: Should I really iterate through each row, as described HERE, or is there a more efficient way?

How do you determine which values in T1 are for `A`, `B` or `C`? — Radu Gheorghiu, Aug 23 '16 at 12:18
@RaduGheorghiu: Please have a look at the pseudo code I provided. — Evgenij Reznik, Aug 23 '16 at 12:20
I'd never iterate through a table, if you have to do that, then your design or algorithm is bad. — Radu Gheorghiu, Aug 23 '16 at 12:22
Your various conditions appear to be mutually exclusive and only depend on data within the current row. I can't see any reason why you wouldn't compute all values in parallel in a single `SELECT` rather than iterating. — Damien_The_Unbeliever, Aug 23 '16 at 12:30

score 2 · Accepted Answer · answered Aug 23 '16 at 12:18

Try something like this in trigger

 ;with data as
 (
SELECT Sum(CASE WHEN col1 = 0 AND col2 = 0 THEN 1 END) AS a,
       Sum(CASE WHEN col1 = 0 AND col2 > 0 THEN 1 END) AS b,
       Sum(CASE WHEN col1 > 0 THEN 1 END) AS c
FROM   (VALUES (0, 0 ),
               (3, 123 ),
               (0, 5 ),
               (8, 432 ),
               (0, 4 ) ) tc ( col1, col2 ) 
               )
UPDATE yt
SET    a = dt.a,
       b = dt.b,
       c = dt.c
FROM   yourtable yt
       JOIN data dt
         ON a.id = b.id

This does not require row by row iteration. Replace the table valued constructor with Inserted table

score 1 · Answer 2 · answered Aug 23 '16 at 12:35

This is something you should not write into a table (unless there aren't millions of rows and you need this for performance...). You should rather get this information on-the-fly like this:

DECLARE @T1 TABLE(col1 INT,col2 INT);
INSERT INTO @T1(col1,col2) VALUES
 (0,0)
,(3,123)
,(0,5)
,(8,432)
,(0,4);

SELECT p.*
FROM
(
    SELECT CASE WHEN col1=0 AND col2=0 THEN 'A'
                WHEN col1=0 AND col2>0 THEN 'B'
                WHEN col1>0 THEN 'C' END AS Category
    FROM @T1 AS t
) AS tbl
PIVOT
(
    COUNT(Category) FOR Category IN(A,B,C) 
) AS p

The result

A   B   C
1   2   2

And I would suggest you to add another option (with ELSE) to catch invalid data (e.g. negativ values).

Iterating through every row efficient?

2 Answers2