"No to SQL"
Every time I see something or hear something like this I sigh a little bit. Not only when it's related to SQL but in the world of computer professionals in general. "The right tool for the job" seems to be a hard concept to understand sometimes. I wonder why?
When you choose a tool there will be pros and cons. Every time. Understand that and you will understand that picking a tool for a job i not about "saying no" or yes to something. If you want to say no or yes go join some political movement/party/whatever.
Would I choose a RDBMS for all of my database needs? Of course not! It all depends on the project. Is the data sensitive? What about integrity? Atomic operations? Type of data? Accessability? Number of users? The number of questions you have to decide on are many and starting by saying "No to SQL" is a pretty darn stupid. In my book that only proves that you do not understand what you're talking about. Trying to argue that X and Y is storing Z amount of data without using SQL only supports the lack of understanding. (Asking "why" X and Y is doing it that way on the other hand is a very good thing.)
Update: The ongoing discussion at ycombinator just shows how silly these kind of "debates" are.
Update 2: High Scalability have also noticed It Must be Crap on Relational Dabases Week.
Common Table Expressions (WITH and WITH RECURSIVE)
Update: Updated note about DB2 version and added a note about Oracle.
Update: Firebird has also implemented CTE.
Going through the presentations from FOSDEM'09 I opened up David Fetter's Trees and More presentation and thought "what could be new about trees?". Man was I wrong!
The presentation shows classical solutions to the problem of trees and hierarchical data in SQL and then moves on to show how this can be solved by the implementation of WITH RECURSIVE in the upcoming PostgreSQL 8.4.
Taking a further look at WITH and WITH RECURSIVE I realised that this is a very powerful tool. Not entirely new, as Depez points out, but still a very usable thing. RECURSIVE is the key part that is new and that could not be solved in a very good way previously.
I have just started to think about what this will mean for our database and so far I've found a case where this is gonna make some difference. Of course I have to make some experiments to verify my theory. :-D
As far as I can tell PostgreSQL is the third RDBMS implementing the WITH and WITH RECURSIVE statement, known as Common Table Expressions or CTE, from the 2003 (WITH) and 2008 (WITH RECURSIVE) SQL-standard. Previously CTE has been implemented by Microsoft, introduced in MS SQL Server 2005, IBM, with DB2 V7, and by Firebird SQL Server, since v2.1.
I thought I could slip under the radar about Oracle but I repent with this note. ;-) Oracle have had WITH since v9.0 and has had CONNECT BY that serves the purpose of WITH RECURSIVE for ages.
I'm not gonna write any examples here since there are a lot of them out there.
Resources
- 7.8. WITH Queries (development docs)
- Depesz on CTE
- Using Common Table Expressions(From MS SQL Server 2008 docs)
(Although I might come back with a little more hands on example after playing around with it. For now I recommend Depez post as it contains a good discussion and example of the feature and what it'll mean for PostgreSQL users.)
