I agree. This think SQL should be kept for querying and basic stuff. People should implement complicated DTS data manipulations in business tier rather than writting really involved cursors etc.

Programming should be though of like when you dig a hole.. If you are playing in the sandpit, you use a spade, if you are digging a hole for a tree you use a shovel, if you are digging foundations for a house, you get an excavator.

You haven't read the O'Reilly Book entitled SQL Cookbook by Anthony Molinaro.

OOh! So I managed to irritate someone with my teddybear-drawing Stored procedure. One thing escapes me though, which language is more suited to drawing teddybears?

I've always felt that it is silly to pontificate about what is, or what isn't a suitable language for the purpose. If it enables the developer to deliver the functionality the customer wants, in the timescales he expects, at a price he can afford, then he shouldn't care what SQL stands for, he should use it.

I distrust sweeping statements about what languages are suitable for this or that. I can remember the high priests of Algol, PL/1, Modula 2, and Lisp going on about how their languages were by far the most suitable for the purpose. Experience proved them wrong and, in hindsight, their claims were ridiculous.

As for 'Business Logic' tiers, in a separate 'layer' to the database, I've never come across one that worked properly and reliably without a great deal of extra expense, effort and delay. It is all down to maintaining transaction and data integrity.

I often meet folks who think that business operations should not take place in the database, or what they hopefully rechristen the 'data-store' or 'data repository'. They often argue the point with great conviction. However I've never heard this kind of talk from people with wide experience building business systems who really understand the issues of transactional integrity, rollback and atomic operations, or who appreciate the efficiency of parallelized set-based operations.

I guess that this all depends on history, or perhaps more accurately, one's historical perspective. I am of the opinion that the late Dr. E.F. Codd got almost everything right in his various articles and most notably in his book, "The Relational Model for Database Management, Version 2" (ISBN 0-202-14192-2). See Chapter 23, "Serious Flaws in SQL", page 371 in the original edition.

Codd invented relatinal databases. SQL was the invention of an IBM committee and bears no relationship to the original theory. If you doubt this, read said chapter. Codd, as I understand it, much preferred Michael Stonebraker's query language.

Either way, your point is right on, IMO. SQL is a query language NOT a programming language. T-SQL, PL/SQL and various other variants are programming languages, with constructs like loops, cursors (gasp), conditional statements etc. None of these constructs exist in SQL. Nor should they: SQL is a query language, NOT a programming language.

A separate question is, Do the .NET extensions satisfy the needs of some application requirements to go beyond T-SQL/PL-SQL etc. to make complex operations simpler to write and simpler to understand? I have seen some truly ghastly T-SQL code, that could be expressed much more elegantly in .NET. With PL-SQL, I have considerably less experience so won't venture an opinion on that.

But at the end of the day, performance is what matters. Given solution A in T-SQL and solution B in .NET code, regardless of the expressive clarity I would ultimately go with superior performance. But I deal with millions of rows, and somtimes 20 joins, so that may colour my opinion on this.

Just my $.02.

Regards,
Artful

I agree 100% with Phil Factor (catchy name by the way). I have written thousands of lines of code in T-SQL and VB and am by trade a SQL DBA. Both are capable tools that have specialties that are sometimes obvious and sometimes not. There is a lot of overlap in capability and Arthur Fuller said in his comments I too have seen some ghastly code - in BOTH languages. I have seen a lot of data manipulation code written with ADO objects that could have been done much more efficiently and with much fewer round trips across the wire with a combination of ADO and stored procs. Many times a solution takes shape in one language or the other simply because the developer is more comfortable in one than the other, which of course, isn't always ideal. It's true, you won't see objects instantiated and inherited properties in T-SQL very often, but you will see data retrieved, manipulated and protected in structs that are more elegant that the closest equivalent in another language. T-SQL definitely has its strong points. It's up to the person writing the code to recognize what they are and know how to leverage them. SQL is not a programming language, but T-SQL definitely is. I think that those who claim otherwise must a few of those .NET programmers who wrote some of the awful T-SQL I've seen in client environments.

OK I admit my comment was intended to provoke a bit of controversy and I'm as guilty as anybody of writing large and complex SQL statements but I tend to draw the lines at looping and I'm gradually weaning myself away from cursors. Sometimes the round trip is the important issue in one thing that I solved - the resultant sql code took about 6 seconds to run but with clever server caching this hardly ever happened. Without all the work being performed on the server the data involved in the round trips was truly astounding. It is best tool for the job and I understand that as I'm quite keen on XSL too. It can't do everything and there are other ways of doing things bearing in mind the limitations it can do a great job.

Very much like SQL - it's a great tool but it shouldn't be abused (eg adventure games ;) ).