SQL, to loop or not to loop?

Question

the problem story goes like:

consider a program to manage bank accounts with balance limits for each customer
{table Customers, table Limits} where for each Customer.id there's one Limit record
then the client said to store a history for the limits' changes, it's not a problem since I've already had date column for Limit but the active/latest limits's view-query needs to be changed

before: Customer-Limit was 1 to 1 so a simple select did the job
now: it would show all the Limits' records which means multiple records for each Customers and I need the latest Limits only so I thought of something like this pseudo code

foreach( id in Customers)
{
  select top 1 *
  from Limits
  where Limits.customer_id = id
  order by Limits.date
}

but while looking through SO for similar issues, I came across stuff like
"95% of the time when you need a looping structure in tSQL you are probably doing it wrong"-JohnFx
and
"SQL is primarily a set-orientated language - it's generally a bad idea to use a loop in it."-Mark Bannister

can anyone confirm/explain why is it wrong to loop? and in the explained problem above, what am I getting wrong that I need to loop?

thanks in advance

update : my solution in light of TomTom's answer & suggested link here and before Dean kindly answered with code I came up with this

SELECT *
FROM Customers c
LEFT JOIN Limits a ON a.customer_id = c.id
  AND a.date = 
    (
       SELECT MAX(date) 
       FROM Limits z 
       WHERE z.customer_id = a.customer_id
    )

thought I'd share :>

thanks for your response,
happy coding

Answer 1

Will this do?

;with l as (
  select *, row_number() over(partition by customer_id order by date desc) as rn
  from limits
)
select *
from customers c
left join l on c.customer_id = l.customer_id and l.rn = 1

Answer 2

I am assuming that earlier (i.e. before implementing the history functionality) you must be updating the Limits table. Now, for implementing the history functionality you have started inserting new records. Doesnt this trigger a lot of changes in your databases and code?

Instead of inserting new records, how about keeping the original functionality as is and creating a new table say Limits_History which will store all the old values from Limits table before updating it? Then all you need to do is fetch records from this table if you want to show history. This will not cause any changes in your existing SPs and code hence will be less error prone.

To insert record in the Limits_History table, you can simply create an AFTER TRIGGER and use the deleted magic table. Hence you need not worry about calling an SP or something to maintain history. The trigger will do this for you. Good examples of trigger are here

Hope this helps

Answer 3

It is wrong. You can do the same by quyting customers and limits with a subquery limiting to the most recent record on limit.

This is similar in concept to the query presented in Most recent record in a left join

You may have to do so in 2 joins - get most recent date, then get limit for the date. While this may look complex - it is a beginner issue, talk complex when you have sql statements reaching 2 printed pages and more ;)

Now, for an operational system the table design is broken - limits should contain the mos trecent limit, and a LimitHistory table the historical (or: all) entries, allowing fast retrieval of the CURRENT limit (which will be the one to apply to all transaction) without the overhead of the history. The table design you have assumes all limits are identical - that may be the truth (is the truth) for a reporting data warehouse, but is wrong for a transactional system as the history is not transacted.

Answer 4

Confirmation for why loop is wrong is exactly in the quoted parts in your question - SQL is a set-orientated language. This means when you work on sets there's no reason to loop through the single rows, because you already have the 'result' (set) of data you want to work on. Then the work you are doing should be done on the set of rows, because otherwise your selection is wrong.

That being said there are of course situations where looping is done in SQL and it will generally be done via cursors if on data, or done via a while loop if calculating stuff. (generally, exceptions always change). However, as also mentioned in the quotes, often when you feel like using a loop you either shouldn't (it's poor performance) or you're doing logic in the wrong part of your application.

Basically - it is similar to how object orientated languages works on objects and references to said objects. Set based language works on - well, sets of data.

SQL is basically made to function in that manner - query relational data into result sets - so when working with the language, you should let it do what it can do and work on that. Just as if it was Java or any other language.