New Relational Sub-Language?
-
What does anyone think of the idea of adding a new relational sub-language to a future version of SQL Server (while continuing to support SQL as well)?
At minimum such a language should include the following features, most of which don't exist today or only have extremely limited support in SQL: relational assignment; relational comparison; all the basic operators of relational algebra; transitive closure; user-defined types; key derivation; generalised state constraints; transition constraints.
Some people might feel that these features could just be added to Transact-SQL rather than as a separate language interface. I'll pre-empt that argument by saying why I disagree. Too many compromises would have to be made in order to integrate truly relational features into SQL's tuple-bag model and the non-relational aspects of SQL syntax. Also, for compatibility and portability reasons it makes sense not to mix SQL and relational language elements in the same space.
My rationale for suggesting this now is that SQL Server has an ever-growing family of sub-languages and "modes of access" both within the DBMS and peripheral to it : MDX, XQUERY, CLR, Entities, LINQ, SSIS. The technology has reached a point where support for a first class relational language is well overdue.
Any comments?
-
David,
Can you explain to a not-so-smart guy on where/why you'd want this language? I'm not quite following the problems it solves, or maybe I just don't understand what you think it would help.
Also, is there anything similar in Oracle/DB2?
-
Steve,
The quick answer is Microsoft will not do that because most of what he is asking for is currently used in packaged software products like Project and MOSS. In his last interview the missing Jim Gray says "we decided to build a database product that meets business needs and Oracle seems to agree. Jim Gray is one of the people who standardized SQL and Oracle's Jim Melton is a member of ANSI SQL. And Microsoft did not create XQuery it got there kicking and dragging because XML was part of ANSI SQL 2003. Microsoft actually implemented XQuery with the Algebraic flaw of the SELECT statement, as Chris Date puts it a SELECT returns a table.
Now there are two books that will help those interested Chris Date's last book very readable under 300 pages and one due out in October that is Relational Algebra heaven hope to take a look when published.
http://www.dba-oracle.com/oracle_news/2005_8_10_date_relational_book_chapter.htm
http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm
Kind regards,
Gift Peddie -
In a nutshell, I'd expect to see all the benefits that the relational model promised but that SQL doesn't always manage to deliver on: derivability, integrity, data independence, guaranteed access.
As for the problems solved, I could pick many examples but here are a few highlights:
1. Integrity constraints. Complex business rules are hard or impossible to implement without procedural code. For example: "Account balance can't exceed the stated credit limit", "Nominal Ledger must balance to zero" or "Mandatory 1 to 1 correspondence".
2. Data warehousing. A billion dollar industry has emerged to solve the problems of DW in SQL. Many of those problems would disappear if we had better data independence. In SQL Server today the only options for persisting data in storage are via tables and indexes. It would be far more practical to make virtual tables (i.e. relational expressions) the basic means of mapping data into storage because that would give us more flexibility in the way data is physically stored and manipulated. For example decision support queries will benefit from column-based rather than row-based storage (see some of the work done by Mike Stonebraker). I believe that a major obstacle to supporting this today is that SQL lacks relational closure, which makes it hard to guarantee consistent updating (witness the limitations placed on the operations permitted in indexed views).
3. Assignment. Solves the "merge" problem. Again, common to very many DW / data integration solutions.
4. Relational Comparison and Division. The family of problems solved by set comparisons is VERY common in SQL DBMSs, yet SQL support is poor. The number of times Joe Celko has discussed his "pilot skills" example online is one measure of how common Division problems are!
5. A better relational engine. By freeing the engine of the burden of supporting duplicates in tables and queries we would get the benefit of much smarter query optimisation and processing. Chris Date has given one example of why optimisation is a hard problem in SQL today (in Database in Depth, Chapter 3).
6. Guaranteed access and transitive closure. = no more cursors. Need I say more!
Anything similar in Oracle or DB2? No, but other relational products are emerging, such Alphora Dataphor. I am not as pessimistic as Gift Peddie about the possibility of MS doing this because A) The technology is better able support it today than ever before, B) The potential demand is growing C) SQL no longer has the monopoly on data access anyway, so the argument for continuing to put up with SQL's foibles only grows weaker.
-
Date's book is a must-read.
Tropashko's book is already out. I got mine here: http://www.bookpool.com/sm/0977671542. I have to admit I was a little disappointed. It does contain some interesting nuggets but there wasn't much that was fresh to me (maybe I've just done too much of this stuff for too long
) -
Hi Can Any one help,
I am new to this forum, so not sure if its the write place to post a question.
Well i got a flat file (in{CR}{LF} format) and needs to be imported into a sql server 2000.
now i need to know how can i create a package using DTS or some thing which kindoff breaks the data down and insert it into appropriate tables from the flat file. I need to find a way to carry it out in one process as the data is imported through the pipe. SO i have multiple tables which would eventaully contain the data from flat file after two weeks. is there any way i can automate the process.
regards
-
I think adding relational logic capabilities to SQL is great idea, for one reason only-
we'd have to rename it to something like Structured Query and Relational Language - SQRL.
I think it'd be a lot more fun to call it Squirrel Server than Sequel Server.
-
I agree! Date and Darwen do too. They included RM Proscription 10 in The Third Manifesto: "D shall not be called SQL"!
-
I do understand and appreciate where your coming from. While the ideas that David is talking about are compelling there has been very little in the way of translating them into practical use. It is even more compelling to actually see what these ideas actually look like in the context of a real problem. I have a blog illustrating what these concepts look like in actual code. I hope you will stop by.
http://www.beyondsql.blogspot.com
-
In the thread:
comp.databases.ms-sqlserver
Jul 25, 6:52 am
'Pass Table as a parameter to a function'
httphttp://tinyurl.com/2j4dvy
Joe Celko makes the following comment on passing a table as a parameter to a procedure:
>> Is it possible to pass a table as a parameter to a function. <<
'Please read a book, any book, on data modeling, and RDBMS. A table is an entity or a relationship. That would mean you have a magical, super function that works on Squids, Automobiles, Britney Spears,
Geographical locations or anything in the whole of creation.'
But the super function, reusable functions, is precisely what modern database developers should have at their disposable! And you can have it:
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html
-
I'm late to the discussion, but here's my cynical two cents anyway...
It will never happen, because if ANYONE ever actually delivered a relational language and platform, why would we need SQL? Answer: we wouldn't.
Why won't anyone, much less Microsoft, deliver a TRDBMS?
Because marketing drives development and the major players are all to busy moving "beyond relational". ...Talk about a phrase that makes me want to puke!
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Hi DCPeterson,
Thanks for reviving the discussion.
I'm not sure I understand your argument. I agree with you when you say that if we had a truly relational language and platform then we could phase out SQL. I don't agree with you that this is a reason for not implementing a relational language. Surely the possibility of SQL being superseded by something better is one of the strongest reasons why Microsoft might want to implement a relational language and providing an upgrade path. SQL's importance is dwindling. I previously listed just some of the data access languages that are already challenging SQL's dominance.
Don't let's confuse "SQL" with "SQL Server". SQL is but one aspect of the product that is for historical reasons known as "SQL Server".
"Beyond relational" is hype and I think it is seen as such. The whole point of those features in Katmai is that they deliver richer type support WITHIN the SQL model (and by extension, within the relational one too).
Even if marketing does rule the roost, I don't see a problem. Who cares what label they give to the new language, as long as it works?
-
DCPeterson (9/25/2007)
I'm late to the discussion, but here's my cynical two cents anyway...It will never happen..
Well it 'has' happened to a significant degree. The product is called Dataphor @
You can store the data in any db, ie. Sql Server, Oracle, DB2.
I blog about Dataphor and what a relational language looks like along with many examples. I also try to constrast this language with sql in the hopes that users can gain a better understanding of each. I encourage you and anyone else who is interested in advancing database and application development to have a look @
www.beyondsql.blogspot.com[/url]
If anyone has any comments/questions/flames/rants please feel free to make your thoughts known anyway you want 🙂
A 'relational' system isn't going to come from a major vendor. But this should not dissuade people from learning the major concepts involved and the benefits they offer users. If anyone can even point out a link demonstrating that any major vendor is even aware of the subject matter please enlighten me 🙂 I have little patience with people advocating something but only if it comes from their adopted vendor family.
-
Aaron McConnell (8/3/2007)
I think adding relational logic capabilities to SQL is great idea, for one reason only-we'd have to rename it to something like Structured Query and Relational Language - SQRL.
I think it'd be a lot more fun to call it Squirrel Server than Sequel Server.
Everyone's got to have a dream, dude.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply