I've heard a lot lately that SQL is a terrible language, and it seems that every framework under the sun comes pre-packaged with a database abstraction layer.

In my experience though, SQL is often the much easier, more versatile, and more programmer-friendly way to manage data input and output. Every abstraction layer I've used seems to be a markedly limited approach with no real benefit.

What makes SQL so terrible, and why are database abstraction layers valuable?

+11  A: 

I wont say it's terrible. It's unsuitable for some tasks. For example: you can not write good procedural code with SQL. I was once forced to work with set manipulation with SQL. It took me a whole weekend to figure that out.

SQL was designed for relational algebra - that's where it should to be used.

Nikolay R
The unfortunate thing is that it's very tempting to just stick some simple procedural code into a stored procedure. And it works fine. UNTIL someone needs to maintain it/add some exceptions, etc...
Brian Knoblauch
-1, err that's precisely the point, SQL is designed to be set-based and that's the power of it. Thinking in procedural terms means you are thinking wrong.
+3  A: 

SQL is not a terrible language, it just doesn't play too well with others sometimes.

If for example if you have a system that wants to represent all entities as objects in some OO language or another, then combining this with SQL without any kind of abstraction layer can become rather cumbersome. There's no easy way to map a complex SQL query onto the OO-world. To ease the tension between those worlds additional layers of abstraction are inserted (an OR-Mapper for example).

Joachim Sauer
why is it SQL's fault that OO languages don't map well to it? When you use a service, conform to its interface or don't use it.
Noone said that it's SQLs fault. The lingua franca of DB-access is SQL. The lingua franca of business logic is an OO-based one. Those two don't match perfectly without some help. No "fault" or "problem" here, just some requirements ("make them match") and a widely-accepted solution ("use a OR-Mapper").
Joachim Sauer
Joachim Sauer said _SQL is not a terrible language, it just doesn't play too well with others_ To me, it does sounds like someone said _it's SQLs fault_
@KM: Are you saying that French and German are bad languages? They don't play all that well with English.
David Thornley
+4  A: 

For one thing, they make it trivial to use parameterized queries, protecting you from SQL injection attacks. Using raw SQL, from this perspective, is riskier, that is, easier to get wrong from a security perspective. They also often present an object-oriented perspective on your database, relieving you of having to do this translation.

True, but you don't need an ORM for this
Using parametrized queries is trivial when writing SQL directly, provided you have a decent database interface layer (i.e., one that supports placeholders). `$dbh->do("DELETE FROM my_table WHERE some_value = ?", undef, $target_value);` There. Done.
Dave Sherohman
I never said you couldn't write parameterized queries with RAW SQL. I said that using raw SQL is riskier since you have to implement it yourself. I've seen many cases of raw SQL code where the query isn't parameterized. ORMs provide this benefit automatically.
True, but avoiding SQL injection is trivially easy even without prepared statements. Every project I do, I write a simple function that puts quotes around a string and properly escapes any embedded quotes. It takes about fifteen minutes to write even if I don't copy it from a previous project. Then I use that for every literal I embed in a query. What's mind-numbing to me is that programmers don't do this! Not take fifteen minutes to avoid deliberate or -- probably more common -- accidental SQL injection! Why not?!
If I were to review any project I'd much prefer to see a tried and tested way to prevent SQL injection (eg a parametrized query) over any custom one.
Jay, does that "simple function that puts quotes around a string and properly escapes any embedded quotes" take into account the current character set in use on the server?
@Jay, you mention prepared statements. Just want to raise a little flag there, prepared statements are not the same as parameterized queries.
Simon Svensson
+20  A: 

It's not so terrible. It's an unfortunate trend in this industry to rubbish the previous reliable technology when a new "paradigm" comes out. At the end of the day, these frameworks are very most probably using SQL to communicate with the database so how can it be THAT bad? That said, having a "standard" abstraction layer means that a developer can focus on the application code and not the SQL code. Without such a standard layer you'd probably write a lightweight one each time you're developing a system, which is a waste of effort.

Trevor Tippins
+2  A: 

I agree with your points, but to answer your question, one thing that makes SQL so "terrible" is the lack of complete standardization of T-SQL between database vendors (Sql Server, Oracle etc.), which makes SQL code unlikely to be completely portable. Database abstraction layers solve this problem, albeit with a performance cost (sometimes a very severe one).

I don't understand how SQL dialect incompatibilities can be such a huge "point" against SQL. That's like saying C is crap because you can't just compile+run the same source in differing Linux distros.
Jeff Meatball Yang
@Jeff: I don't think it's a huge point against SQL, actually. That's why I said "I agree with your points" and put "terrible" in quotes. I prefer SQL over data abstraction layers, myself, although I'm glad things like NHibernate exist because they're *much* better than the home-grown crap that used to proliferate.
True - I use abstraction layers too - I guess I meant to say that, to me, the SQL language is not the problem - it's the transformation of normalized data into objects, which is why abstraction layers are useful.
Jeff Meatball Yang
+51  A: 

One point of abstraction layers is the fact that SQL implementations tend to be more or less incompatible with each other since the standard is slightly ambiguous, and also because most vendors have added their own (nonstandard) extras there. That is, SQL written for a MySQL DB might not work quite similarly with, say, an Oracle DB — even if it "should".

I agree, though, that SQL is way better than most of the abstraction layers out there. It's not SQL's fault that it's being used for things that it wasn't designed for.

Joonas Pulakka
+1 great point, this is especially valuable when migrating from one DB env to another.
I don't understand how SQL dialect incompatibilities can be such a huge "point" against SQL. That's like saying C is crap because you can't just compile+run the same source in differing Linux distros. If, and it's a big IF, your company decides to switch database vendors, it's a rare and big occurrence that has more moving parts than just rewriting some SQL.
Jeff Meatball Yang
It's not a point against SQL, it's a point *for* the abstraction layers. Like, you can't just compile+run the same C code just anywhere, it's a point *for* more platform indifferent languages. But it doesn't make SQL nor C "crap": the abstraction layers run top of the deeper layers, anyway.
Joonas Pulakka
@Jeff: In C, common tasks are part of the standard. In SQL, it's impossible to paginate a result set without getting into vendor specific SQL.
R. Bemrose
Oh, and about the rarity of switching database vendors: what are you talking about? Does the rarity of a company switching operating systems render cross-platform solutions irrelevant? You don't always know beforehand what your product is going to be run on, so it's better to err towards more generic solutions.
Joonas Pulakka
@Jeff: The customer does not change the database, but your customers prefer to have different databases.
Stefan Steinegger
I'm in the unhappy position of developing I certainly want to move us in that direction. It's not that SQL is bad (it's really good for what it's intended to do). It's that there's a severe mismatch between relational models and object models. A good abstraction layer lets you program to the data, not to the underlying model. That's really where we need to be. LINQ is great for that.
Cylon Cat
@Joonas: I guess I meant to say that the SQL language(s) is(are) not the problem - The question asked what makes SQL so terrible, and my initial reaction, like yours, is that it's not. But I wanted to argue that accommodating different dialects is not really the point of ORMs - we'd have them even if we only had one standard language - I think it's more that we need a way to resolve normalized data to an OO-model.
Jeff Meatball Yang
@Jeff: True, but one could reply that perhaps the reason we change platforms so rarely is because it is so difficult. Right now I'm working on a Java-based project where the developers routinely do all their development and testing on Windows, and then we deploy to Linux for production, and I have yet to see a problem that could be traced to the platform. But the system was originally developed with Postgres and we'd like to switch to a different db engine ... but that's just wildly impractical. Why should that be?
I have to say that I upvoted only because you mentioned Oracle. Seems like every single DB related tool or library I've found always mentions **Oracle** as the huge exception to just about everything.
+82  A: 

This is partly subjective. So this is my opinion:

SQL has a pseudo-natural-language style. The inventors believed that they can create a language just like English and that database queries will be very simple. A terrible mistake. SQL is very hard to understand except in trivial cases.

SQL is declarative. You can't tell the database how it should do stuff, just what you want as result. This would be perfect and very powerful - if you wouldn't have to care about performance. So you end up in writing SQL - reading execution plans - rephrasing SQL trying to influence the execution plan, and you wonder why you can't write the execution plan yourself.

Another problem of the declarative language is that some problems are easier to solve in a imperative manner. So you either write it in another language (you'll need standard SQL and probably a data access layer) or by using vendor specific language extensions, say by writing stored procedures and the like. Doing so you will probably find that you're using one of the worst languages you've ever seen - because it was never designed to be used as an imperative language.

SQL is very old. SQL has been standardized, but too late, many vendors already developed their language extensions. So SQL ended up in dozens of dialects. That's why applications are not portable and one reason to have a DB abstraction layer.

But it's true - there are no feasible alternatives. So we all will use SQL for the next few years.

Stefan Steinegger
"Just state what you want - we deliver as soon as possible". That declarative approach is fantastic, and it's actually modern (think LINQ). It's true sometimes you need to tweak for speed, and a procedural alternative to SQL might be useful then. But I'd still be using declarative SQL most of the time for the simplicity.
MarkJ: I remember that I reordered WHERE clauses to optimize queries in oracle. They where resolved from bottom to the top... terrible.
Stefan Steinegger
Being an ex-COBOL programmer, I equate writing SQL with writing COBOL. VERY similar languages in concept (if not actual syntax).
Brian Knoblauch
I agree completely. IT people have this fascination with non-procedural tools. Wouldn't it be so much easier if you just tell the computer what you want, and it figures out how to do it! Yeah, if the computer was actually smart enough to do that. But it isn't. I'd like it if I could just tell my car "Take me to grandma's" and it would drive me there. But it doesn't, so I don't just let go of the steering wheel and pretend this will work. Maybe someday the technology will be there, or maybe that's an impossible dream. But it isn't there today. (continued ...)
I've had the same experience, of knowing exactly what a good execution plan would be, the database generates a terrible one, and then I have to tinker with the query to try to trick it into generating the right plan!
How many times have you really had to tune SQL? In two years of working in a large corporate IT environment, I've had to tune SQL (using query hints) ONCE! In MOST situations the computer does figure it out for you and figure it out reasonably well.
C. Ross
Touché. Your answer perfectly describes my early frustrations with SQL. I wrote a lot of procedural code because I didn't trust SQL to generate an efficient execution plan. As I grew more conversant in SQL, I discovered that it is in fact quite good at optimizing and that properly written SQL usually runs faster than my procedural code.
Most SQL DBMS products allow you to specify execution plans via either query hints or abstract query plans
Matt Rogish
"resolved from bottom to the top". Older versions of Oracle? I still miss the rule-based optimizer...
Mike Woodhouse
@Mike: Yes, older version of Oracle. I think it was version 8. I didn't use oracle since a while - still like it though, Sql Server is not better at all :-)
Stefan Steinegger
@Kludge: Sometimes the optimizer can do better than you would be likely to do with procedural code because it can take into account relative sizes of tables, distribution of keys, etc. You could, I suppose, write procedural code to do all that but to do it on every query would be a lot of work, and the whole idea of the optimizer was supposed to be that this was done for you in one place. The catch is that in practice, optimizers often come up with terrible plans. I rarely look at a plan and say, "Oh, yeah, that's much better than what I was thinking." Very often I say, "You stupid optimizer!"
Hmmm, all you execution-plan-bashers should remember that most engines generate the actual plan considering the current schema and *data* already stored. If wanted to do the same in a higher layer it'd be impossible or at least suboptimal, not to mention counterproductive.
Wojciech Kaczmarek
@Jay and the other SQL doubters. The issue in my experience is that to use SQL effectively you need to be able to think in terms of sets and not procedurally - which is precisely how most programmers are taught to think. Using SQL effectively really isn't that hard and well within the reach of any competent coder (like anyone reading SO!) but it is necessary to make the effort to jump to thinking in set-based logic, and much of the time people don't bother (and I'm currently amending a program where the original coder has done everything using cursors for precisely this reason)
@cruachan: Absolutely true about "sets" vs "procedural" (though I'm not sure that those are proper antonyms -- it sounds like asking "Did you eat fish today or did you wear a blue shirt?") The question is, is a set-based approach superior to a procedural approach, is a procedural approah better, or does each have its place? In my experience, for simple queries the set-based approach is easier and often more efficient than what most programmers could do procedurally. But as problems get more complex, the SQL optimizers just aren't smart enough to figure out how to do it efficiently.
+1  A: 

try saying that over here...

Thems fighting words! :P

LOL, just made me laugh out loud IRL.
Mark Schultheiss
+3  A: 

SQL is a really good language for data manipulation. From a developer perspective, what I don't like with it is that changing the database don't break your code at compile time... So I use abstraction which add this feature at the price of performance and maybe expressiveness of the SQL language, because in most application you don't need all the stuff SQL has.

The other reason why SQL is hated, is because of relational databases.

The CAP Theorem becomes popular:

What goals might you want from a shared-data system?

  • Strong Consistency: all clients see the same view, even in presence of updates
  • High Availability: all clients can find some replica of the data, even in the presence of failures
  • Partition-tolerance: the system properties hold even when the system is partitioned

The theorem states that you can always have only two of the three CAP properties at the same time

Relational database address Strong Consistency and Partition-Tolerance.

So more and more people realize that relational database is not the silver bullet, and more and more people begin to reject it in favor of high availability, because high availability makes horizontal scaling more easy. Horizontal scaling gain popularity because we have reached the limit of Moore law, so the best way to scale is to add more machine.

If relational database is rejected, SQL is rejected too.

Nicolas Dorier
+15  A: 

SQL is designed for management and query of SET based data. It is often used to do more and edge cases lead to frustration at times.

Actual USE of SQL can be SO impacted by the base database design that the SQL may not be the issue, but the design might - and when you toss in the legacy code associated with a bad design, changes are more impactive and costly to impliment (no one like to go back and "fix" stuff that is "working" and meeting objectives)

Carpenters can pound nails with hammers, saw lumber with saws and smooth boards with planes. It IS possible to "saw" using hammers and planes, but dang it is frustrating.

Mark Schultheiss
+1  A: 

I'd agree with most of the posts here that the debate over the utility of SQL is mostly subjective, but I think it's more subjective in the nature of your business needs.

Declarative languages, as Stefan Steinegger has pointed out, are good for specifying what you want, not how you want to do it. This means that your various implementations of SQL are decent from a high-level perspective : that is, if all you want is to get some data and nothing else matters, you can satisfy yourself with writing relatively simple queries, and choosing the implementation of SQL that is right for you.

If you work on a much "lower" level, and you need to optimize all of that yourself, it's far from ideal. Using a further layer of abstraction can help, but if what you're really trying to do is specify the methods for optimizing queries and so forth, it's a little counter intuitive to add a middleman when trying to optimize.

The biggest problem I have with SQL is like other "standardized" languages, there are very few real standards. I'd almost prefer having to learn a whole new language between Sybase and MySQL so that I don't get the two conventions confused.

You can save yourself quite a bit of that pain by just not using MySQL. ;)
Steven Huwig
+2  A: 

Living with pure SQL can really be a maintenance hell. For me the greatest advantage of ORMs is the ability to safely refactor code without tedious "DB refactoring" procedures. There are good unit testing frameworks and refactoring tools for OO languages, but I yet have to see Resharper's counterpart for SQL, for example.

Still all DALs have SQL behind the scenes, and still you need to know it to understand what's happening to your database, but daily working with good abstraction layer becomes easier.

dealing with instances of objects in memory is quite different than the data that is physically stored in a database. there is more pain fixing poor designs, and possibly massive variations in performance based on "little things"
+28  A: 

SQL gets badmouthed from several sources:

  • Programmers who are not comfortable with anything but an imperative language.
  • Consultants who have to deal with many incompatible SQL-based products on a daily basis
  • Nonrelational database vendors trying to break the stranglehold of relational database vendors on the market
  • Relational database experts like Chris Date who view current implementations of SQL as insufficient

If you stick to one DBMS product, then I definitely agree that SQL DBs are more versatile and of higher quality than their competition, at least until you hit a scalability barrier intrinsic in the model. But are you really trying to write the next Twitter, or are you just trying to keep some accounting data organized and consistent?

Criticism of SQL is often a standin for criticisms of RDBMSes. What critics of RDBMSes seem not to understand is that they solve a huge class of computing problems quite well, and that they are here to make our lives easier, not harder.

If they were serious about criticizing SQL itself, they'd back efforts like Tutorial D and Dataphor.

Steven Huwig
Regarding the first point about programmers not being comfortable with anything but an imperative language. It'll be interesting over the next few years to see if/how the resurgence of functional programming makes any difference to this. There's a lot of hype at the moment over languages like Haskell, F# and Scala making developers that much more productive. This type of "mathematical" thinking for programmers is very similar to the the knowledge of relational algebra and tuple relational calculus that SQL pre-supposes. Maybe there will be a resurgence of native SQL set-based thinking in time!
Trevor Tippins
I should clarify that I meant "those programmers not comfortable with anything but imperative programming," not that all programmers are uncomfortable with it.
Steven Huwig
Yeah Chris Date certainly knows what he's talking about, still The Third Manifesto didn't get to mainstream yet since 14 years. Which reminds me of the question: does anybody use Rel or Dataphor for something serious? That would be interesting..
Wojciech Kaczmarek
+1, well said. And as someone who spent there first few years as a professional coder in the pre-relational database I find it frankly stunning that anyone wants to go back to that era except for specialized tasks. A day spent writing code traversing a DL/1 tree structure should be enough to convince anyone.
The trouble is that there are many compulsive programmers, who would rather bang out a few hundred lines of insipid code rather than think about things for an hour or so.
Steven Huwig
+5  A: 

I'm a huge ORM advocate and I still believe that SQL is very useful, although it's certainly possible to do terrible things with it (just like anything else). .

I look at SQL as a super-efficient language that does not have code re-use or maintainability/refactoring as priorities.

So lightning fast processing is the priority. And that's acceptable. You just have to be aware of the trade-offs, which to me are considerable.

From an aesthetic point of view, as a language I feel that it is lacking some things since it doesn't have OO concepts and so on -- it feels like very old school procedural code to me. But it's far and away the fastest way to do certain things, and that's a powerful niche!

Brian MacKay

SQL isn't a bad thing. The layers use it to get data just like you would. They give you safe datatyping and the ability to deal with items as objects instead of raw text. If you did not use an abstration layer that is fine but you will end up creating your own, even if it is not as robust as a product dedicated to that.

+47  A: 

Aside from everything that was said, a technology doesn't have to be bad to make an abstraction layer valuable.

If you're doing a very simple script or application, you can afford to mix SQL calls in your code wherever you like. However, if you're doing a complex system, isolating the database calls in separate module(s) is a good practice and so it is isolating your SQL code. It improves your code's readability, maintainability and testability. It allows you to quickly adapt your system to changes in the database model without breaking up all the high level stuff, etc.

SQL is great. Abstraction layers over it makes it even greater!

Miguel Ventura
Very diplomatic! (And true, to boot!)
The trouble is abstraction inversion. SQL in a relational database is a set-based declarative environment. It has a higher level of abstraction than imperative programming, object-oriented or no.
Steven Huwig
Maybe so, Steven, but in terms of the application it performs a low level function (even if it does it in an extremely high level way). No matter what crazy transformations you do at the database level, at the end of the day it is a glorified getter and setter. Or you could look at it the opposite way, in that it is too high level to be mixed in with the application and must be sequestered and considered separately. In any case, keeping the SQL outside of the main application code has very tangible benefits in terms of readability and refactoring.
+2  A: 

If you haven't used SQL too much, I think the major problem is the lack of good developer tools.

If you have lots of experience with SQL, you will have, at one point or another, been frustrated by the lack of control over the execution plan. This is an inherent problem in the way SQL was specified to the vendors. I think SQL needs to become a more robust language to truly harness the underlying technology (which is very powerful).

Jeff Meatball Yang

I don't dislike SQL, but I also don't want to have to write it as part of what I am developing. The DAL is not about speed to market - actually, I have never thought that there would be a DAL implementation that would be faster than direct queries from the code. But the goal of the DAL is to abstract. Abstraction comes at a cost, and here it is that it will take longer to implement.

The benefits are huge, though. Writing native tests around the code, using expressive classes, strongly typed datasets, etc. We use a "DAL" of sorts, which is a pure DDD implementation using Generics in C#. So we have generic repositories, unit of work implementations (code based transactions), and logical separation. We can do things like mock out our datasets with little effort and actually develop ahead of database implementations. There was an upfront cost in building such a framework, but it is very nice that business logic is the star of the show again. We consume data as a resource now, and deal with it in the language we are natively using in the code. An added benefit of this approach is the clear separation it provides. I no longer see a database query in a web page, for example. Yes, that page needs data. Yes, the database is involved. But now, no matter where I am pulling data from, there is one (and only one) place to go into the code and find it. Maybe not a big deal on smaller projects, but when you have hundreds of pages in a site or dozens of windows in a desktop application, you truly can appreciate it.

As a developer, I was hired to implement the requirements of the business using my logical and analytical skills - and our framework implementation allows for me to be more productive now. As a manager, I would rather have my developers using their logical and analytical skills to solve problems than to write SQL. The fact that we can build an entire application that uses the database without having the database until closer to the end of the development cycle is a beautiful thing. It isn't meant as a knock against database professionals. Sometimes a database implementation is more complex than the solution. SQL (and in our case, Views and Stored Procs, specifically) are an abstraction point where code can consume data as a service. In shops where there is a definite separation between the data and development teams, this helps to eliminate sitting in a holding pattern waiting for database implementation and changes. Developers can focus on the problem domain without hovering over a DBA and the DBA can focus on the correct implementation without a developer needing it right now.

Downvoter - care to explain why or just clicking the down button for everything you don't agree with?
+2  A: 

SQL has many flaws, as some other posters here have pointed out. Still, I much prefer to use SQL over many of the tools that people offer as alternatives, because the "simplifications" are often more complicated than the thing they were supposed to simplify.

My theory is that SQL was invented by a bunch of ivory-tower blue-skiers. The whole non-procedural structure. Sounds great: tell me what you want rather than how you want to do it. But in practice, it's often easier to just give the steps. Often this seems like trying to give car maintenance instructions by describing how the car should perform when you're done. Yes, you could say, "I want the car to once again get 30 miles per gallon, and to run with this humming sound like this ... hmmmm ... and, etc" But wouldn't it be easier for everyone to just say, "Replace the spark plugs" ? And even when you do figure out how to express a complex query in non-procedural terms, the database engine often comes up with a very inefficient execution plan to get there. I think SQL would be much improved by the addition of standardized ways to tell it which table to read first and what index to use.

And the handling of nulls drive me crazy! Yes, theoretically it must have sounded great when someone said, "Hey, if null means unknown, then adding an unknown value to a known value should give an unknown value. After all, by definition, we have no idea what the unknown value is." Theoretically, absolutely true. In practice, if we have 10,000 customers and we know exactly how much money 9,999 owe us but there's some question about the amount owed by the last one, and management says, "What are our total accounts receivable?", yes, the mathematically correct answer is "I don't know". But the practical answer is "we calculate $4,327,287.42 but one account is in question so that number isn't exact". I'm sure management would much rather get a close if not certain number than a blank stare. But SQL insists on this mathemcatically pristine approach, so every operation you do, you have to add extra code to check for nulls and handle them special.

All that said, I'd still rather use SQL than some layer built on top of SQL, that just creates another whole set of things I need to learn, and then I have to know that ultimately this will be translated to SQL, and sometimes I can just trust it to do the translation correctly and efficiently, but when things get complex I can't, so now I have to know the extra layer, I still have to know SQL, and I have to know how it's going to translate to I can trick the layer into tricking SQL into doing the right thing. Arggh.

The whole relational database idea was the product of ivory tower blue-skyers. Early relational databases had horrible performance compared to the then-current hierarchical databases, and took a long time to get established. The power of the abstraction was such that hierarchical databases are essentially a thing of the past (not that there aren't likely IMS and CODASYL databases out there still). It had to have worked very, very well.
David Thornley
But management wouldn't see a "close if not certain number" - they would see a wrong number. Maybe that final customer owes a *lot* of money. Then management would be very unhappy about that wrong number.
RoadWarrior: Yes, it's possible that you have 10,000 customers who each owe you $10 and 1 customer who owes you $10 million. But if that was the case, anyone requesting an AR report would likely know that customer's name very well and know exactly what was going on with them. Okay, I'm sure there are times when no answer at all is better than an answer that is so unreliable as to be meaningless. But that's my point: SQL is designed around theoretical considerations like that: a dogmatic "anything less than perfection is worthless". ...
... In real life, 95% of the time, an approximate answer is way better than a blank stare. When I look at my checkbook, I know that it is quite possible that I've made an arithmetic error or forgotten to write in a check. The balance might well be an approximation. But still, if I know I have "about $1,000" I will have no qualms about writing a check for $50, and I will realize that writing a check for $10,000 will be futile.
Well, I've run a business, and it's never 10K versus 1. IMX, it's more akin to 20 unknown for every 100 known (the pareto principle). And some of those 20 owed us a lot of money, which was actually why the actual amount was in dispute. Again, this is the pareto principle.
RoadWarrior: I was just making a hypothetical example, but okay. Let's say 20% of accounts owed are in dispute. You ask your IT people for the total amount you are owed. Which would you rather hear: (a) "I don't know", or (b) "About $5 million" ? I would think better still would be something like "An undisputed $4 million plus disputed amounts of about $1 million". Then if you want the details on the disputed amounts you get the full report. I'm hard pressed to think of a reason for preferring to not hear the information that is available. Provided, of course, that you are aware it is partial.
The SQL `SUM` function *does* ignore `NULL`. But it defines the sum of an empty set as `NULL` instead of the mathematically sensible 0.
@dan04: Yes, it's kind of amusing that after telling us that any number plus null is null, they then turn around and write the SUM function to say "but in this case, null counts as zero". sum(price)+sum(tax) is not the same as sum(price+tax) if any price or tax is null. The handling is inconsistent.
+3  A: 

Quick, write me SQL to paginate a dataset that works in MySQL, Oracle, MSSQL, PostgreSQL, and DB2.

Oh, right, standard SQL doesn't define any operators to limit the number of results coming back and which row to start at.

R. Bemrose
Why are you trying to target all of those environments with your code? Write me C code for threads that works in Mac OS 9, Windows NT, OS/2 Warp, and Solaris.
Steven Huwig
@Steven Huwig: I'd probably use an abstraction layer to do it for me... which is exactly what the question was asking.
R. Bemrose
@Steven: I do happen to use frameworks and abstraction layers for quite a few things where I need to be platform independent. However, being database independence is just so much more important in most cases. Even if you are only delivering your software to run on Windows, once you get to sell it to bigger corporations you are going to run into everything from "We prefer OSS, do you support MySQL" to "We use Oracle|MSSQL|whatever as a corporate standard, do you support it".
+4  A: 

SQL is excellent for certain kinds of tasks, especially manipulating and retrieving sets of data.

However, SQL is missing (or only partially implements) several important tools for managing change and complexity:

  • Encapsulation: SQL's encapsulation mechanisms are coarse. When you write SQL code, you have to know everything about the implementation of your data. This limits the amount of abstraction you can achieve.

  • Polymorphism: if you want to perform the same operation on different tables, you've got to write the code twice. (One can mitigate this with imaginative use of views.)

  • Visibility control: there's no standard SQL mechanism for hiding pieces of the code from one another or grouping them into logical units, so every table, procedure, etc. is accessible from every other one, even when it's undesirable.

  • Modularity and Versioning

Finally, manually coding CRUD operations in SQL (and writing the code to hook it up to the rest of one's application) is repetitive and error-prone.

A modern abstraction layer provides all of those features, and allows us to use SQL where it's most effective while hiding the disruptive, repetitive implementation details. It provides tools to help overcome the object-relational impedance mismatch that complicates data access in object-oriented software development.

Jeff Sternal
+1 for the link. I knew there was a term for this, and I knew it had to be on Wikipedia somewhere!
Jeff Meatball Yang
+4  A: 

For experienced SQL programmer the bad sides are

  • Verbosity
  • As many have said here, SQL is declarative, which means optimizing is not direct. It's like rallying compared to circuit racing.
  • Frameworks that try to address all possible dialects and don't support shortcuts of any of them
  • No easy version control.

For others, the reasons are that

  • some programmers are bad at SQL. Probably because SQL operates with sets, while programming languages work in object or functional paradigm. Thinking in sets (union, product, intersect) is a matter of habbit that some people don't have.
  • some operations aren't self-explanatory: i.e. at first it's not clear that where and having filter different sets.
  • there are too many dialects

The primary goal of SQL frameworks is to reduce your typing. They somehow do, but too often only for very simple queries. If you try doing something complex, you have to use strings and type a lot. Frameworks that try to handle everything possible, like SQL Alchemy, become too huge, like another programming language.

[update on 26.06.10] Recently I worked with Django ORM module. This is the only worthy SQL framework I've seen. And this one makes working with stuff a lot. Complex aggregates are a bit harder though.

+3  A: 

SQL is based on Set Theory, while most high level languages are object oriented these days. Object programmers typically like to think in objects, and have to make a mental shift to use Set based tools to store their objects. Generally, it is much more natural (for the OO programmer) to just cut code in the language of their choice and do something like or object.delete in application code instead of having to write sql queries and call the database to achieve the same result.

Of course, sometimes for complex things, SQL is easier to use and more efficient, so it is good to have a handle on both types of technology.

Peter Bailey
+6  A: 

I've heard a lot lately that SQL is a terrible language, and it seems that every framework under the sun comes pre-packaged with a database abstraction layer.

Note that these layers just convert their own stuff into SQL. For most database vendors SQL is the only way to communicate with the engine.

In my experience though, SQL is often the much easier, more versatile, and more programmer-friendly way to manage data input and output. Every abstraction layer I've used seems to be a markedly limited approach with no real benefit.

… reason for which I just described above.

The database layers don't add anything, they just limit you. They make the queries disputably more simple but never more efficient.

By definition, there is nothing in the database layers that is not in SQL.

What makes SQL so terrible, and why are database abstraction layers valuable?

SQL is a nice language, however, it takes some brain twist to work with it.

In theory, SQL is declarative, that is you declare what you want to get and the engine provides it in the fastest way possible.

In practice, there are many ways to formulate a correct query (that is the query that return correct results).

The optimizers are able to build a Lego castle out of some predefined algorithms (yes, they are multiple), but they just cannot make new algorithms. It still takes an SQL developer to assist them.

However, some people expect the optimizer to produce "the best plan possible", not "the best plan available for this query with given implementation of the SQL engine".

And as we all know, when the computer program does not meet people's expectations, it's the program that gets blamed, not the expectations.

In most cases, however, reformulating a query can produce a best plan possible indeed. There are tasks when it's impossible, however, with the new and growing improvements to SQL these cases get fewer and fewer in number.

It would be nice, though, if the vendors provided some low-level access to the functions like "get the index range", "get a row by the rowid" etc., like C compilers let you to embed the assembly right into the language.

I recenty wrote an article on this in my blog:

+4  A: 

Heard a lot recently? I hope you're not confusing this with the NoSql movement. As far as i'm aware that is mainly a bunch of people who use NoSql for high scalability web apps and appear to have forgotten that SQL is an effective tool in a non "high scalability web app" scenario.

The abstraction layer business is just about sorting out the difference between Object Oriented code and Table - Set based code such as SQL likes to talk. Usually this results in writing lots of boiler plate and dull transition code between the two. ORM automates this and thus saves time for business objecty people.


I'm not going to read all the answeres, so if someone said it already, oh well.

SQL is not a terrible language.. it just doesnt to everything one would LIKE it to do. For instance.. 2 simple tables and 1 relationship between them. Now.. I've defined the relationship.. but SQL offers me no way of USING it. I still have to write a JOIN (or WHERE clause) to instruct SQL in the manner in which the two tables should be related.

Wouldn't it be nice if we could just write SELECT t1.ID, t1.Name, t2.Whatever FROM table1 t1, table2 t2 USING t1_t2_relationship

Anyhow.. the promise of tomorrow huh? LINQ kind of allows this. I like LINQ.


SQL is geared to work with relational data. C# / Java / C++ are object oriented languages. So if your application is written in one of the object oriented (or procedural) languages you will need to use a layer of abstraction over SQL. Your choice is to either use an existing one or rolling your own.

Another point to remember is - quite a few products are database agnostic. Let us say you build this killer CRM application and you have fine tuned SQL to work with DB2 and ONLY DB2 due to gratuitous use of DB2 specific SQL extensions. Now how do you sell this application to a company that has already invested millions in licensing and support fees for their Oracle databases?

+4  A: 

I would say that a database abstraction layer included with a framework is a good thing because it solves two very important problems:

  1. It keeps the code distinct. By putting the SQL into another layer, which is generally very thin and should only be doing the basics of querying and handoff of results (in a standardized way), you keep your application free from the clutter of SQL. It's the same reason web developers (should) put CSS and Javascript in separate files. If you can avoid it, do not mix your languages.

  2. Many programmers are just plain bad at using SQL. For whatever reason, a large number of developers (especially web developers) seem to be very, very bad at using SQL, or RDBMSes in general. They treat the database (and SQL by extension) as the grubby little middleman they have to go through to get to data. This leads to extremely poorly thought out databases with no indexes, tables stacked on top of tables in dubious manners, and very poorly written queries. Or worse, they try to be too general (Expert System, anyone?) and cannot reasonably relate data in any meaningful way.

Unfortunately, sometimes the way that someone tries to solve a problem and tools they use, whether due to ignorance, stubbornness, or some other trait, are in direct opposition with one another, and good luck trying to convince them of this. As such, in addition to just being a good practice, I consider a database abstraction layer to be a sort of safety net, as it not only keeps the SQL out of the poor developer's eyes, but it makes their code significantly easier to refactor, since all the queries are in one place.

+1 for point 2.

"Yeah, if the computer was actually smart enough to do that. But it isn't. [...] Maybe someday the technology will be there, or maybe that's an impossible dream. But it isn't there today."

Anyone who felt tempted to vote this up, try and answer the question "Does there exist a relational DBMS today that allows me to just declare just any database constraint, and have the DBMS enforce that constraint for me, without me having to do any programming (not even triggers) ?

If your answer is "No", then you are just plain idiots. The answer is "yes".

The future is always possible. And for every problem that is today unsolvable, there exists a point in time where that problem will no longer be unsolvable. But the idiots who are too preoccupied with "I know that this works and I'm going to stick with it" will not see that when it happens, focussed as they are on their horse-glasses vision of what was possible yesterday.

Erwin Smout
Are you willing to be more specific instead of calling people idiots?
Wojciech Kaczmarek
+2  A: 

There's no love for SQL because SQL is bad in syntax, semantics and current usage. I'll explain:

  • it's syntax is a cobol shrapnel, all the cobol criticism applies here (to a lesser degree, to be fair). Trying to be natural language like without actually attempting to interpret natural language creates arbirtrary syntax (is it DROP TABLE or DROP , UPDATE TABLE , UPDATE or UPDATE IN , DELETE or DELETE FROM ...) and syntactical monstrosities like SELECT (how many pages does it fill?)
  • semantics is also deeply flawed, Date explains it in great detail, but it will suffice to note that a three valued boolean logic doesn't really fit a relational algebra where a row can only be or not be part of a table
  • having a programming language as the main (and often only) interface to databases proved to be a really bad choice and it created a new category of security flaws

Since SQL is not a general-purpose programming language, database applications must use some other language for the rest of the implementation. Developers who know the host language but not SQL often like to be able to do SQL-like things in the host language. Also, an abstraction layer can take care of the repetitive process of converting data between the a representation that is natural for the database, and a representation that is natural for the host language. These motivations would still exist even if SQL did not have portability issues.

Greg Graham
+3  A: 
How does that make the syntax not orthogonal?
The language *could* have been be designed so that all these imperative statements have a more common syntax, especially between the `insert` and `update` operations, which are almost identical semantically, but completely different syntactically.
Agreed. The historical reason for the current SQL syntax was a failed attempt to mimic natural language, which was dictated by someone's idea of bringing it to non-programmers, which was not clever concept at all.
Wojciech Kaczmarek

I'll stand up for SQL. It's best if you can think in terms of sets. I prefer to isolate it in stored procs - for security and seperation.

+2  A: 

IMO, the problem that I see that people have with SQL has nothing to do with relational design nor the SQL language itself. It has to do with the discipline of modeling the data layer which in many ways is fundamentally different than modeling a business layer or interface. Mistakes in modeling at the presentation layer are generally much easier to correct than at the data layer where you have multiple applications using the database. These problems are the same as those encountered in modeling a service layer in SOA designs where you have to account for current consumers of your service and the input and output contracts.

SQL was designed to interact with relational database models. There are other data models that have existed for some time, but the discipline about designing the data layer properly exists regardless of the theoretical model used and thus, the difficulties that developers typically have with SQL are usually related to attempts to impose a non-relational data model onto a relational database product.


Having worked with non C/S databases (ISAM) for years it took me a while to become proficient at preparing queries. I'm not talking about the easy queries for CRUD. I am talking about the complex reporting that require joins, unions, nested queries, conditional column expressions etc.

SQL is a language yes but it is not comparable to a procedural programming language it is more analogous to HTML. With HTML you say here browser, render this. With SQL you say here database server fetch or update this data.

I have always weaved layers of abstraction in my code as it suits the application as I have always created reusable libraries for this purpose. But if you depend on others abstractions and avoid a good understanding SQL itself your app will pay the price in terms of performance.


Database abstraction layers are valuable because they allow your code to interact with the database system. SQL isn't terrible - it's just that some people don't understand it, or want to work in one language (e.g. C# instead of C# and SQL). Database abstraction helps separate responsibilities (e.g. programmer and DBA). If you prefer SQL, put all the weight and focus on that side by coding everything in SQL using stored procedures and function for logic, and only use the abstraction layer to make simpler calls into it for your program's purposes.

John K

Many posts here seem to argue that SQL is bad because it doesn't have "code optimization" features, and that you have no control over execution plans.

What SQL engines are good at is to come up with an execution plan for a written instruction, geared towards the data, the actual contents. If you care to take a look beyond the programming side of things, you will see that there is more to data than bytes being passed between application tiers.


Machine code is not a bad language. You can write any program you want in it. But there are plenty of good reasons to want an abstraction layer above it. Sometimes higher level abstractions are easier to maintain, faster to develop in, and easier to understand.

Peter Recore

SQL isn't too bad at the query level when you get the knack of it, but writing stored procedures is painful in any SQL dialect. It's like programming in GWBASIC with SQL support hacked in.


While SQL does get the job done it certainly has issues...

  • it tries to simultaneously be the high level and the low level abstraction, and that's ... odd. Perhaps it should have been two or more standards at different levels.
  • it is a huge failure as a standard. Lots of things go wrong when a standard either stirs in everything, asks too much of implementations, asks too little, or for some reason does not accomplish the partially social goal of motivating vendors and implementors to produce strictly conforming interoperable complete implementations. You certainly cannot say SQL has done any of that. Look at some other standards and note that success or failure of the standard is clearly a factor of the useful cooperation attained:
    • RS-232 (Bad, not nearly enough specified, even which pin transmits and which pin receives is optional, sheesh. You can comply but still achieve nothing. Chance of successful interop: really low until the IBM PC made a de-facto useful standard.)
    • IEEE 754-1985 Floating Point (Bad, overreach: not a single supercomputer or scientific workstation or RISC microprocessor ever adopted it, although eventually after 20 years we were able to implement it nicely in HW. At least the world eventually grew into it.)
    • C89, C99, PCI, USB, Java (Good, whether standard or spec, they succeeded in motivating strict compliance from almost everyone, and that compliance resulted in successful interoperation.)
  • it failed to be selected for arguably the most important database in the world. While this is more of a datapoint than a reason, the fact that Google Bigtable is not SQL and not relational is kind of an anti-achievement for SQL.

You can see a detailed list of SQL Flaws here.

Why the negative vote? The list in the link is very detailed and complete...

I'm a Database Analyst so SQL is very much my bread and butter. I've learned SQL pretty much from scratch in the last 3 years and, while it is sometimes difficult to get to grips with initially, once you know how to use it, you can do some pretty amazing things with it. There are some things I can do in SQL that I haven't been able to duplicate in L2S.

Having said this, I am also a fan of ORM. I've only got in to using Linq to SQL and have not yet made the jump to using Entity, NHibernate, or any other ORM.

As well as my main job, I also do a lot of programming. When working with SQL Server databases, I have found L2S to be invaluable in my productivity. I can put together a working database driven application a lot faster than I did before when dealing directly with SQL, especially when doing inserts, updates, and deletes.

SQL is not a bad language, but when programming I much prefer to just be programming, not flipping back and forth between VB and SQL every 2 seconds. There are also the already stated plus points of type-safety, design-time error-checking, and many more. I no longer have to manually map datatables to classes for my program. If I work correctly, I can get L2S to do that for me. My personal favourite feature of L2S is the ability to do lots of small queries which get submitted to the database as one big one. I don't have to create a massively complicated SQL query when I want to do something difficult, I can break it down in to logical chunks and have the ORM build it in to a single big query to execute.

SQL and ORM both have their places, ORM is just another addition to the Programmer's toolkit.