SQL is a great language, and I’m discovering new things about it daily, but it has a big problem, and that is that it is hard to express some simple problems with SQL. Let’s take a contrived example, my Payments table now has a column that mention the number of payments to be made, and I want to get the total of all the individual payments. A first implementation would look like this:

SELECT sum(amount / NumOfPayments) from payments

But this produce an error: Divide by zero error encountered.

Ouch, so this way is out (and yes, I know that this is a bad way to model the data). So, we have a problem, and the easiest thing to do it to fall to a procedural mindset and code the following:

DECLARE
      @current amount NUMERIC,
      @current NumOfPayments NUMERIC,
      @total NUMERIC,
      @tmp NUMERIC

DECLARE big payments cursor CURSOR

            FOR SELECT amount,NumOfPayments FROM Payments

SET @total = 0

OPEN big payments cursor

FETCH NEXT FROM
      big payments cursor INTO @current amount, @current NumOfPayments

WHILE @@FETCH STATUS != -1
BEGIN

IF (@current NumOfPayments IS NOT NULL AND @current NumOfPayments <> 0)
      SET @tmp = @current amount/@current NumOfPayments    
ELSE
            SET @tmp = @current amount

      SET @total = @total + @tmp   

      FETCH NEXT FROM
            big payments cursor INTO @current amount, @current NumOfPayments

END

CLOSE big payments cursor
DEALLOCATE big payments cursor

SELECT @total

This works, and even produces a decent enough answer, and that is where some “predecessors” may leave the code. But I decided to take it into a spin over a million records (no indexing, Primary Keys or Foreign Keys whatsoever).

Do you care to know what the execution time for this? 01:03 Minutes! That is not good. But do I have a choice? Well, it turn out that I do, check this out:

SELECT SUM(
      CASE NumOfPayments
            WHEN NULL THEN Amount
            WHEN 0 THEN Amount
            ELSE Amount/NumOfPayments
      END
) FROM Payments

Care to guess how fast this run over the same table? 00:00, too fast for the clock to count. Next time, I’ll talk about nested selects and how to use them in even more complex queries.