By "set based" I'm referring to the way SQL does things the most efficiently. It works best on sets of records, not on individual records (or rows). Looping through the BO (in SQL Server, cursors) is row based. Row based manipulation provide lots of power as to what can get done, but is not as fast (generally) as using SQL to manipulate a whole set at once (that is not as fast for SQL server to get the work done. Languages like .NET are more efficient at row based manipulation. Which is fastest would probably depend on all sorts of variables, like efficiency of code, hardward, connectivity, ect.).
Set based manipulation is great if you have something you want to do to the data based on some criterion. E.g. lets say you have a database containing a table of movie rentals. You want to mark all rentals more than a year old as needing maintenance (like polishing the DVDs). It is more efficient to use an update statement than to loop through the rows:
Update rentals -- table with rentals
Set MaintenanceStatus = 1 -- lets say this means it need maintenance
Where PurchaseDate <= DateAdd(year,-1,GetDate()) -- gets date one year ago
Since this sort of thing only needs to be done infrequently, the user doesn't actually need to see the data to get the job done, a SQL statement is the way to go. You could create a sproc (for max speed, as it would be precompiled in SQL) or you could just create a method in a BO.
However, there are times when this won't work too well. If what you need to do is complicated or not data related or the data is in another database or the user needs to see all the data to decide what to do (the data would thus be on the client anyway), then row level processing is needed. It turns out that it isn't actually needed very often though.
I hope this helps. I come from MS Access background, not a VFP, so I had to learn a lot about SQL Server as I had it doing lots of work to speed up things. Sounds like VFP was speedy (always heard that, but I wasn't allowed to use it when I started doing db development...long story). The whole disconnected data thing in .net is different and I'm still getting used to it. It may be that this world view is now inaccurate or not as accurate, as it might be more efficient to use a loop in .net than in SQL server. I'm watching this post, hoping to learn a lot also
