In defence of SQL
seldo.com
seldo.com
"Computer, give me a list of orders for red sweaters, but skip ones from customers who signed up more than 3 months ago"
... and it will figure out how to get you that list. And the syntax is so simple that you can do pretty much everything you'd ever want with six keywords. The query to get the list above is actually shorter than its English language description.
Whenever I see some new ORM come out with its own way of asking for records from a database, a little question mark pops up over my head. Why would you do that? Did you really spend time building something to make your problem harder? Your code less elegant? Why why why?
The only thing I can imagine is that people simply don't bother to learn SQL before deciding they don't like it. Too bad too, because writing queries is one of the most enjoyable bits of the software development game.
Performance wise too, nothing beats hand-optimizing queries rather than hoping that the ORM of your choice doesn't decide that the particular method of retrieving the total value of a specific order will need one query per line item plus manually calculating the total.
[tangent] That's part of the attraction to NoSQL as well. You just serialize your objects to JSON or something else (probably already being done if you target the web). It's simple.
Back to ORMs, you can't avoid SQL wrappers at least to help w/ injections, and you probably want a layer that serializes/deserializes your objects as well. Once you've written that a few times it's boring so you abstract it. ORMs are pretty much inevitable when writing the common web app.
ORMs should borrow good things from SQL, like LINQ. Mainstream languages are better now than when SQL was created. I mean, it's not the exact syntax you like right? I prefer writing code rather than preparing and parsing strings. Plus if you ever decide to swap out the backend it's far easier when things are loosely coupled.
SQL is akin to JS in the browser in that you need a library to abstract away the implementation differences or you'll be tightly coupled to a specific one. Maybe you don't need to run on all backends simultaneously but options are always nice.
It all depends what the task is anyway though.
In addition, over time, the official SQL standard got better and better (quite unlike EcmaScript which took a 10 year break and then returned with minimal improvements) and the databases began following the standard more and more as being standards compliant became a selling feature (which is also quite unlike the ecmascript case where not even the innovative browsers support all of the new features from ES5)
Hence, I would claim that the need for an SQL abstraction layer is way smaller than it is for client-side JS
That's not unique to ORM. We don't use an ORM at Expat, yet all our objects still have .Load() and .Save() methods. I hope you didn't read my post to mean I advocate hand-writing your CRUD!
I think LINQ is a great example of needlessly re-inventing something that wasn't very hard in the first place. Especially since its syntax is so close to SQL. Why not simply write your ad-hoc reports in SQL itself and save a lot of trouble?
But since I often work with large collections of heterogenous data outside of a DB, I use it all the time to transform and filter collections in ways that would be very tedious in other languages. Libraries can get you some of the way there, but the language integration is what makes it so painless.
Dynamically creating queries is amazing in LINQ. Building them with strongly typed expressions is much better than messy string concatenation.
As you say, strongly typed dataobjects are pretty cool. Regardless of how they interface with the database.
The problem here is that you stop thinking about your data as an independent thing that must be designed for long-term persistence.
Code itself should be mutable, easily changed and modified. The on-disk data is the opposite of this -- it must be iterated on carefully, and its requirements must be clearly defined as to be supported in the long-term.
I'm very wary of tools that do not make it easy to explicitly and rigorously define the data model entirely separately from the object model that will be used to represent it in a specific version of one application.
I think tools like LINQ are valuable because they make working with relational algebra and projections cleaner, but do not abstract the fact that you are working with a long-term persistent data store that is not the same thing as your comparatively short-lived object graph.
For example: suppose you want to write a SQL script that populates a database with dummy data. There's no way to do it without dynamic sql, since SQL doesn't let you parameterize table and column names.
Many programmers do love SQL. It's not a perfect solution, but it's better than most of the via alternatives.
Anyone that "hates" SQL should be forced to read C.J. Date's Database in Depth, after which, if they still hate SQL, it will at least be for the right reasons.
http://www.amazon.com/Database-Depth-Relational-Theory-Pract...
But there are places where you don't really want named attributes with the names known beforehand, you want to generate new attributes, perhaps named on the fly, perhaps designated ordinally. The relational model doesn't really have room for this. The various flavors of SQL offer bolted-on ways to do this, incompatible & quite different. But it's a living...
What we need is a more elegant, flexible language instead of SQL for data retrieval. SQL++, since the relational model is sound. Beyond that, we need a more intelligent data store that will be able to translate a fully normalized schema into a high performance, distributed, persistent representation that is queryable by this language.
There's no reason in theory this can't happen, its just hard. To think that dropping us down to GET and PUT operations is the answer is just saying "we give up."
I don't want some data in a relational model. Yes, I could put it there, but I would be doing so solely so that it fits in the relational model. Which is pretty pointless.
The fact that the relational model is "sound" doesn't mean that everything belongs in it. Plus, I'm not holding my breath waiting for an "intelligent data store that will be able to translate a fully normalized schema into a high performance, distributed, persistent representation that is queryable by this language". That's the description a mythical database panacea that solves all problems with no downsides.
The continual pitting of NoSQL vs SQL is a gross mis-characterization perpetuated out of holy-war fervor, yet based on few facts or even understanding of the differences.
Does GNU invite being pitted against its namesake by hardcore oldtime Unix fans?
Still, focusing on whether 'NOFOO' name is appropiate or not is quite silly.
Almost all data fits in the relational model. And it's an excellent way of organizing data. A lot of NoSQL solutions are simply unorganized blobs. That might be fun at the beginning of projects (or for small projects) but it's a step in the wrong direction.
However, unless I had a really large number of documents, I wouldn't do this again. With a schemaless solution, you're just moving the problem of schema changes into the code and whatever structure your data was at any time you have to support forever. Right now, I have conditional branches in code to support an document structures that haven't been relevant in years. In SQL, you can alter the table and write a quick query to update your data and you're done. Whatever mistakes you made in your design are history.
Proponents of schemaless storage claim that it's great for development but I disagree. I change my design constantly -- add tables, remove tables, split columns, you name it. I don't hinder myself because my data is organized into typed columns. I alter the data as needed to fit the new structure. As a benefit, I never have to support my previous mistakes.
If you are not migrating your data to your current schema, it will decay to garbage. If you are, you already have the old and new schema in your mind, so why not write them down and get some help from the tools?
The other way out is to keep the authoritative version of your data in a store with a schema, and maintain a summary of it in your low-latency store that you can regenerate at need.
I'd like to see more relational, column-based databases, with better query languages - I like the relational model, but find SQL to be a bad compromise at best. I think the distinction is lost on a lot of the people caught up in NoSQL hype, though.
The database I described is not a panacea, you'd still have to do both good modelling and describe the ways to optimize the materialization of your data. It doesn't make the core challenges of data architecture any simpler, you still need a brain. It does, however, provide a clean abstraction and would in theory maintain most of the good things that RDBMSes have brought us without throwing all the hard work of our elders in the trash can.
Yea, but it's not the best or the easiest to represent all forms of data. Just because a problem can be solved with the relational model doesn't mean it should be; there are many ways to skin a cat and sometimes non relational solutions work better for the problem at hand.
Sure there is. I won't attempt some silly proof because it should be obvious that tradeoffs exist in all software. Supporting ad-hoc relational queries restricts the types of optimizations that are possible. Every index you add is going increase write times and yet they may be necessary for some queries. SSDs open up some doors, but still, everything about the way a database is designed is a tradeoff.
I think the point of NoSQL is that nowadays with web software, often we have very simple data that needs to scale massively, and it is just easier to build some functionality with rougher primitives than it is to try to tease the performance out of an SQL database which may not offer fine-grained enough control to offer the basic functionality we need in the performance profile we want.
To think that dropping us down to GET and PUT operations is the answer is just saying "we give up."
SQL database technology is approaching 50 years old. It's had countless billions of dollars and man hours poured into its optimization. Big DBs like Oracle can do amazing amazing things. Even today development continues rapidly on SQL database of all types, both new and old. I'm not sure how a few people working on alternate data stores with radically design criteria can be called "giving up".
That said, there's still some stuff to be learned from things like BigTable and Dynamo that simply weren't considered when building Oracle due to CAP. I'd wager that what you're seeing now is the dark ages of where we regress back to infancy, forgetting the lessons of the past (throwing out the schema? really?) before someone comes along and solves the root problems correctly.
Edit: Oh, and to your other point about tradeoffs. I'm not saying there aren't tradeoffs involved. The point is, the "tradeoff" should be "oh, please configure this table to not support custom WHERE clauses by materializing it into a simple K-V store. Thank you Mr. Database", or "relax consistency here so we can have availability across data centers on this table, thank you Mr. Database." But these should be easily described and diverge from the default, ACID-compliant, normalized, schema'd, and relationally-queryable data storage.
Looking at JQuery as a query language was a breath of fresh air, and made me realize how wonderful a query language can be, and how capable the software community can be.
The relational model is not a sane way to store a book, a compressed song, a code base, or many other things.
SQL databases are a great way to store some kinds of data.
The relational model is not a way to store anything since there are no implementations which are both faithful to the model and solid data stores.
Real world SQL data stores are suboptimal in different ways for almost all tasks, so progress is possible.
Ultimately a disks (and memory) is get and put. Anything model we put above that makes some assumptions, and they may be the wrong ones.
So where are we going. Here is what I think: requiring the articulation of the domain twice, once for a datastore, and once for application, is spitting on humanity in a grotesque and obscene way, so SQL must die.
One of the main points of the article is that if you don't have a good handle on what assumptions you need to make about your data, choose a relational database. That is because it makes the fewest assumptions about the kinds of questions you will need to ask about your data. If you pick a different solution that does not support the kinds of questions you would like to ask, you will experience much pain.
We have had a stagnant SQL monoculture as a default choice for data, for 20 years.
Given a project of with an uncertain future we are now starting to get other choices.
We know that using an SQL databases will create pain. Suffering that pain in the hope that later questions may be answered less painfully, is becoming a mindful decision. Especially in startups, not the default that it has been in the enterprise world.
Google seems to be a lot better at answering questions than Sybase is. Out of date, offline, full text indexing is not something SQL databases do well.
For content like movies, music and large documents, we simply don't (and should not) create a relational model, we use big blobs and dump them into whatever tools we have around.
We all would agree that if your data looks like source code you are better off with git than with MySQL. So where else does "if you don't know use SQL" breakdown.
However I think ORMs are too quickly dismissed. In the simplest case, an ORM can appear to be a key-value store at the API level. Of course that is a gross abuse of a relational store, but there's no reason it can't be done. Additionally, the author fails to note that an intelligent ORM can provide a caching layer that improves performance over raw SQL. A more detailed examination of the various patterns to resolve the impedence mismatch, such as active record versus data mapper, ORM caching strategies, and how those compare to a key-value store solution, I think would be a valuable topic of discussion.
Why not use two tools where two are needed?
Many databases run a mix of OLTP (which requires fast cache and no ad-hoc queries) and ad-hoc business intelligence queries.
Queries are programming, the worst kind, programming without composable abstractions.
What people actually mean when they say this is they like having a workspace and a live environment. It's like working in Smalltalk or Lisp in a live image.
Put your email in your profile, I'm disappointed you didn't continue the discussion yesterday after saying do it over email, which you don't make available.
Source: I'm currently a SDE intern in the SQL Server team at Microsoft.
There is a rigorous notion of .net object.
There's a rigorous notion of a .NET object but it's not compatible with Java's, C++'s, Smalltalk's, ...
The early design for any system should be done in your database's diagramming tool. Figure out a data structure that holds the information you're application is going to use, and once you've got that you've also got your object model.
You can then generate your whole backend by pointing your favorite code generator at the database schema. You'll end up with the same User, Customer, BlogEntry or whatever objects you would have come up with if you'd just started coding from scratch, but they'll come with a .Save() method that stores them away in a data model that fits them perfectly.
I mean, don't get me wrong, this is what I do, too (I only started using MongoDB over the weekend after years of MySQL and Postgres), but the similarity to the arguments is pretty interesting.
Personally, I find that starting from the schema makes one more agile (small 'a'), not less so.
I guess the answer is to just make your domain objects glorified structs. I've never liked that answer, as it seems to defeat the whole point of object oriented design.
I disagree. In my opinion, the birth of ORM lies in the fact that a lot of people want to work on problems that have almost nothing to do with SQL but need persistence. They want to spend time working with objects in their domain model and just have persistence that works.
So if that's the case, why did ORM even appear? Why didn't everyone just spend time figuring out "NoSQL"/key-value stores/object stores and move away from RDBMS? 1) SQL and relational data stores, as the author himself points out, are trustworthy and standard. People may not like working with them or be intimidated by them, but most people can at least figure out simple joins and things. The relational model may not be the best answer for some things, but it at least gets the job done. 2) Just because I want to work with my domain objects in my application, and just because I'm short-sighted and all I care about at the moment is enabling a business idea or solving an interesting problem with my app, doesn't mean that I won't want to "get answers" from my data in the future. Maybe it won't be me - maybe it'll be another group in my company with more RDBMS knowledge than I have that's interested in data-mining my store, and because I've used an RDBMS, it will work extremely well.
I don't think developers are scared of SQL, I think they just don't care about working with it because it doesn't have much to do with what they're interestd in. Maybe ORM isn't a perfect solution, but it lets me have my cake (persistence that gels with my domain model) and eat it too (a relational backend when we need BI and reporting).
I just don't see how the pure SQL people and ORM people are philosophical opponents, other than how ORM tools make dealing with SQL (which is an awesome, proven technology) so much easier, I suppose it could theoretically be a threat to their livelihood. The way I see it, they are partners in the same cause.
I'm the same as you, I write applications that need to store and interact with data. The difference between using an ORM or not is like light and day, and for me, this constitutes 90% of my work. Certainly, there are situations where it is just common sense to drop down to raw SQL (and if your ORM doesn't support this, you are screwed), but people like the OP seem to subscribe to the "throw the baby out with the bathwater" philosophy of life....if you can find a single problem with a solution, throw the whole thing out.
This is one of those arguments that will never, ever go away. I used to be passionate about the subject, but I don't care much anymore. The anti-ORM people will continue to say it doesn't work (ever), while the pragmatists will continue to use ORM because it does work, and for the edge cases, they will drop down into pure SQL, which most developers know very intimately anyways.
On a related topic, how many DBA's have you met that understand SQL, query plans, etc deeply, and have a strong knowledge of database system tables (as well as you do as a developer?) Most DBA's I've met basically follow: http://en.wikipedia.org/wiki/Argument_from_authority
Most shithead queries I encountered during years of DBAing were written by hand by poor folks abandoned by their colleagues and over-confident managers.
Just no one cared to educate them, but everyone expected "progress" from them. So they did what they could..
In fact I wished many times that they used some abstraction layer which would save them from doing such harm, simply because this layer would be written by more educated people.
Bingo. Every time I've seen someone hating on an ORM, it's always a DBA.
You wouldn't believe the number of queries that I've written by hand while thinking "ActiveRecord would have just written this for me" because An ORM Couldn't Possibly Be Performant Enough For Us!
Databases do two things: they persist data, and they answer queries about data. Neither of those requirements put any restrictions on how they serialize data. Relational storage, object storage, document-based storage, key-value storage, graph-based storage, etc.—it all just seems like different optimizations on the same "real" data, something that should be abstracted away from the database administrator entirely.
Why can't we just have a database that gets fed a normalized, relational-algebra-style model of the data, but then profiles the kinds of queries fed to it, and the kinds of data given, and builds whatever kind of denormalized tables/indexes/k-v stores are optimal for your usage patterns, using a Relational, or Object DB, or Graph DB, etc. engine when each is appropriate (perhaps firing them off in parallel and returning whichever answer comes first, killing the other threads)? Is this something anyone is working on?
That'd be so cool. I wish RDBMSs came with query optimizers. Maybe something that could ANALYZE (or, better yet, EXPLAIN) their performance, so people could tune it.
And yeah, sarcasm aside, it's easier to have a DBA tune things than some AI - of course: "Why can't we just have a compiler that gets fed a description of the problem, then solves it, calculating the optimal solutions for all of the engineering trade-offs, and then builds whatever kind of interfaces are optimal for your users choosing Design Patterns as appropriate. Is this something anyone is working on?"
There's a big, unaddressed opportunity here: if you can just make your DB smart enough to tune itself 81% correctly (and that's not very hard—certainly not AI hard) then you can conquer the market for people who don't know how to EXPLAIN their way out of a paper bag. It's not like it would be inventing its schema on the fly; you'd still be feeding it a formal, completely-specified but unoptimized set of tables. It would just do things like:
* Notice when it gets a query with the same parameters 10000x in a row, and grind out an index for it;
* Notice when a large (>100000) set of rows in a table is identical save for their primary key, and only queried by COUNT(foo), and turn those rows into a single row with a foo_count field (and a log table in case a query ever does come in that cares about the IDs);
* Notice when two tables are only ever queried joined together, and morph the primary store for them into a denormalized table, turning the previous, separate tables into views on the denormalized table.
And so on. If you look at, say, Python's hashtable library, it has all sorts of these little "rules of thumb" for when to alter its internal representation, because of what it guesses the user is up to (storing unique pairs, creating mathematical sets, nesting hashes to mimic a complex data structure, etc.) Databases, on the other hand, much bigger systems purely for flexibly storing and querying data, just stubbornly sit there until the DBA (because there must always be a DBA, even if the DB is just being fed migrations written by Rails CRUD coders) comes along to tell them to do the things they already know would make them faster. It's just silly.
That is not the hard part of my job. That is the easy part. No thank you.
And, consider: if it's the easy part, why are [some] people switching to NoSQL just to avoid doing it? ;)
Part of writing the CREATE TABLE query you mention is hooking up primary and foreign keys and setting up indexes. That's just how you build databases. Even if you never look at it again, you still need to do it.
I'd just replace "at some shops" with "at most shops" to reflect reality a bit more.
I learned SQL while I was learning to program. My very first (real?) program was a php hit counter that wrote to a hits.txt on the server. My second one was a guestbook that used mysql. My third one was a basic hierarchical forum. I wasn't a wizard, I was just a kid reading a book, bored at the Pennsylvania Farm Show.
Thus, I have spent my fair share of time working with sql queries in php code. Guess what? The SQL queries weren't very hard at all.
However, SQL Queries had almost nothing to do with actual using SQL itself. 90% of the code written in such a project is guaranteed to be cruft code surrounding the sql queries. Special cases abound. The difference between "column = $val" and "column is null" is so painful that I just can't imagine going through that anymore.
And that's just the first example! What does "" mean if you save it in a db column? Should you save it as a null value, or as a string with zero characters?
I just don't care about this at all, and you'd have to be a damned fool to think that I would ever willingly go back to doing that bollocks.
I already have my in-memory data structure laid out. ORM lets me keep from worrying about it it again (at least not nearly to the detail I would with SQL). With an ORM, 90% of the database code that I used to have to deal with is simply a non issue.
The point against ORMs is not that they have inferior APIs -- in fact I say how bad SQL is. The point is that they wrap quite nice APIs around an inappropriate data store. If you have an object-like in-memory data structure, why not use an object store and have it in your data as well? The relational nature of your store is working against you.
If your ORM exists to keep 90% of the database code hidden, why not dispense with the RDBMS entirely and lose that useless 90%?
When I hear people psyched about the whole NoSQL thing but actually quite ignorant about relational databases (like a guy who gave a presentation on CouchDB at a barcamp here last year), I understand why people sometimes call it the "NoMySQL" movement - a lot of the hype seems to come from people whose experience seems couched in PHP+MySQL tutorials, specifically.
I don't believe that there really are all of these developers around who
lack knowledge of SQL.
To me, the people that you are talking about have no context in the discussion in the least. They would be the last to jump on the NoSQL bandwagon because they don't really care, for example.
The universe of discourse only includes the devs who care, e.g. the people who are creating ORMs, using ORMs, using NoSQL, etc, not _all_ developers. I kinda assumed that was implied.
I'd say that SQL is a COBOL of relational algebra...
* I've heard it can run $60k/CPU. It's designed for realtime stock data mining (https://cs1.cs.nyu.edu/cs/faculty/shasha/papers/hpts.pdf), so they get away with it.
I meant to reply to this, didn't notice until after the edit window closed...
http://nsl.com/papers/kisntlisp.htm
This C code helped me get into the headspace. (http://nsl.com/papers/origins.htm)
Chris Date is worth reading for his ideas on what a good language for relational algebra should be like and why SQL isn't that language.
The notion that people steer away from SQL because it is intimidating is absurdly ignorant.
The communities that embrace ORMs and NoSQL are the ones who are learning and creating new things all the time. No one's first language is Ruby, they didn't learn node.js in college, they didn't take an (N)Hibernate certification course. They learn in their free time!
Meanwhile, if you were good in SQL 12 years ago then you still are today!
Smarter people than me have written on these subjects, but for me, the answer is simple. As Fowler says, the most pervasive smell in software is duplication. If I litter my code with all the CRUD SQL (even worse with sprocs that contain column sizes) then my software is quite brittle.
If I hard code filenames all over my code, I also have horrible code. This doesn't mean that files and file names are a problem or that they caused duplication.
The normal (I hope) practice is to this in terms of transactions or activities and create functions that wrap those activities. SQL should only live within those functions that provide a logical interface to the DB. Its called TAPI or XAPI (transaction API). Think of it as your own domain specific ORM.
Which is generally repetitive and full of duplication, exactly the thing that drives people to use ORM's in the first place. It doesn't matter that it's isolated into a separate layer, it's still more manual work than an ORM.
I'm not clear on why this layer has to be repetitive and full of duplication.
Let's say you have a database with 100 tables in it. How many insert statements might there be, just a rough estimate, in your layer of functions? My ORM has 1. At the most basic level, a simple ActiveRecord ORM has 1 insert, 1 update, 1 detele, and 1 select statement. How many are in your typical app? If it's more than 1 of each, then it's duplication.
I haven't written an insert/update/delete statement in years because I don't need to. When I need a fancy select that's an aggregate of several tables, I write a view and then map it into a list of objects with the ORM. This is virtually no work, I spend all of my time working on the actual problem at hand and virtually none marshaling data in or out of the database.
How can multiple insert statements be considered duplication if they are inserting different things to different tables? My apps have multiple insert statements. They also have multiple calls to "print". Do you count that as a duplication as well?
We can continue the discussion by email if you prefer. It is interesting to me, but we may be getting off topic.
Because they're all the same pattern of code. Whenever you see common patterns, you're missing an abstraction that can greatly simplify things.
Why would I write 100 different insert functions when I can write one that generates itself dynamically using reflection over whatever object it is given? You only need one insert statement, it just needs to be smarter rather than static hand written code.
You need only tag your objects with a little bit of metadata using attributes to give a generic insert/update/select/delete everything it needs to dynamically generate parameterized statements on the fly for every table in the db that you use. Put simply, the only thing I need to do to use a User table in an app is write something like the the following...
class User : ActiveRecord<User> {
[PersistentString(MaxLength=50, Required=true)]
public string UserName;
[PersistentString(MaxLength=50, Required=true)]
public string Password;
[PersistentString(MaxLength=5000)]
public string About;
public User(){}
}
Rule One is "Don't touch the CRUD".
The code you write touches the auto-generated base classes that wrap the auto-generated CRUD. It never need concern itself with the duplication you speak of.
Overall, you may also want to use orm for sakes of interchangeability of your SQL as well, and it certainly gives you advantages.
Im working on a project that involves no orm and uses oracle as a primary data store. Though I don't want to install oracle On my machine but repository idea built-in to project doesn't let me use postgre for development. And yet schema creation involves varchar2 that's simply inaccessible in postgre. Its quite easy to fix but I'd expect to have some kind of a fallback....
In any case, it's a good point that key-value storages are not for every problem, but that's quite obvious. someone said that every time you say "nosql", a kitten dies. so, careful there.
Try doing that with an ORM or a key/value store.
SQL error messages are very unhelpful in every DB. You can't easily debug misbehaving queries and there are no tools to help. There is even a lack of indentation standard.
Fixing very long queries that fetch the wrong results is something I've been doing daily for the past 10 years of my life. It is not easy, and I believe the language is to blame for a large part of the difficulty. In some aspects of doing unexpected things with no warning - it is worse than C.
SQL is very difficult to debug in general, which combines rather poorly with the previous characteristic. Perhaps there are nonstandard extensions in Oracle that make it easier but there's nothing I know of in the core language.
SQL has fooled people into thinking it is equivalent to the Relational Model, when in fact as kunley points out elsewhere it is merely inspired by it.
SQL dates from the 1970s and it shows. There are syntax quirks around every corner. The behavior of NULL is sensible, but it is merely one of several sensible choices and there are reasons why the SQL choice is not the one that has "won". Why is "value IN ()" a syntax error? It's perfectly reasonable. What's with the syntactic drama around subselects, which ought to be a core part of the better language that we really ought to be using? (A language in which the equivalent is probably going to be so simple we won't even believe people had a special syntax for it.) What's with the rigid specification of the order of GROUP BY and ORDER BY and everything else, when those should simply be instances of some sort of combinator that applies in order, in a sensible manner. Why is SELECT used both for data queries and aggregate queries when in fact the two operations are so different they should have entirely different keywords? Why is it so hard to mix an aggregate and non-aggregate query together when it's obvious to me exactly what I want?
Why can't my columns contain tables themselves, recursively? The fact that there are databases that can do this proves it's not impossible.
SQL needs to be replaced by a combinator-based language that backs to a richer data store that is more sensible, both from a relational model point of view and from a recursive point of view. LINQ points the way, but still is hobbled by LINQ-to-SQL being the flagship LINQ product. (And I don't think it's complete as-is.)
But why do you want columns that contain tables recursively?
Aggregate and non-aggregate mixture is supported in the standard and Oracle with the analytical expressions (if I got your meaning correctly).
SELECT ... FROM customers LEFT JOIN orders ...;
To get this into a presentable table, I would have to keep track of the previous customer's ID and only start a new row if the current customer ID is not equal to the previous customer ID. If table-valued columns were available, each customer row would have a column containing a table of their orders.
Nested tables may well be the bee's knees, but probably not for this reason alone...
This is the sort of question you ask because you've become so used to the way SQL does things that you can't even think outside the box. (No offense; this is SQL's fault, not yours.) If I recall correctly, the Relational Model fully permits this. And once you start thinking about it, you can see that there are numerous places where you're basically trying to do things like this, and it would be much easier if you could have tables in rows, but instead you have to put a series of weird hacks in. Anytime you have a table that participates in multiple many-to-many relationships, it would be a lot cleaner if SQL could just say "Look, this ID has no meaning except inasmuch as it identifies another set of rows somewhere else, so how about we just cut to the chase and say that those rows are just here?" The joins could be the default and magic access to the ID could be the thing you have to type extra to get.
"Aggregate and non-aggregate mixture is supported in the standard and Oracle with the analytical expressions (if I got your meaning correctly)."
What I mean was more a syntactic issue than a question of whether it's possible; there's very poor separation between the two, so combining them is even harder.
(And crap, the design for my combinator-based language is starting to coalesce in my head. Damn it, that wasn't supposed to happen, I was being vague on purpose....)
This is really annoying. If I have a stored proc for getting data w/ 3 optional parameters for filtering it, I have to either use dynamic SQL (building the query by string concatenation), or have 8 different versions of the query for each combination of filter present/not present. The dynamic SQL part might not even work with bind parameters, because EXECUTE IMMEDIATE / sp_executesql cannot take a dynamic number of bind parameters (0 to 3 bind parameters, depending on the filter).
I have to wonder why none of the database vendors ever created a syntax-tree API for SQL queries. They have the parser right there, so why can't they do something like
q := PARSE(SELECT x, y, z FROM sometable);
q.ADD_CONDITION(PARSE(sometable.value = some_dynamic_value));
EXECUTE q;I think that that is a bit of convenient revisionism as it becomes clear that the Emperor is wearing no clothes.
There absolutely was a very antagonistic, "this changes EVERYTHING" philosophy behind the movement called NoSQL, and the name is not accidental. Perhaps it was just a bit of trolling to get attention, but in no way was it a live and let live initiative (data tactics that are now bound in "NoSQL" long existed in computer science, since before the days of SQL. They just didn't feel the need to make it a comfortable community of evangelists). It was a "that was yesterday and this is today".
Of course things have changed. A bit of realism entered the mindset, so now it's more of a "Heh heh...no bad blood right? We're all good, eh?"
"it was one of 3 or 4 suggestions that I spouted off in the span of like 45 seconds, without thinking."
Spur of the moment isn't really accidental, any more than Mel Gibson accidentally said the N-word. There's a bit of a history and a thought process that leads to such a decision.
I'd have to totally agree with this. SQL also achieves higher level of abstraction than any of the above.
Perhaps, as the author said, there is a chance that SQL (or subset of it) will be used as the query language for No SQL-type back-ends.
Isn't that a tautology?
Standard just meant that it worked in lots of different places. It didn't mean that it was the only option, first option, or best option. But, by being standard, it became a lowest common denominator that people largely stuck to.
The links at http://en.wikipedia.org/wiki/Query_language include many of the competitors to SQL. In particular look at Datalog, ERROL, ISBL, and QUEL.