maybe the problem could be caused by the incompetents who continue to be completely unable to discern the difference between a “development” language and a “data manipulation” language?
sqlserver is for grades 8 through 12. If you are a developer, than you can bear it, but for administrators, especially if you have prior experience in the Linux Oracle environment, this is not an enterprise product. The OS and database are absolutely dreadful.
I couldn’t imagine going back to coding applications in ‘C’, Java, or (God help me) COBOL! I have a saying – “If you can explain your problem in English, you can code it in SQL and get an answer. If you can’t explain your problem in English, then you don’t need a developer at all!”. I don’t miss the endless error-checking (OPEN FILE…did it open? READ FILE…did I get a record? CLOSE FILE…did it close?) at all!
Once we start analyzing what is happening inside the database for the commands/reserved words we hit against the database we get really interested in the way the db works/responds; really. So, the concept is very important and the efficient and optimum way in writing sql for user requirement is really an art! It involves lot of math, reasoning,… improves aptitude for every one!
ANdrew C |
Having written thousands of SQL statements for reports on Oracle (and Access and MySQL and MSSQL) I find SQL easy. I’ve actually found it easier to mentally picture my result sets as a sort of Venn diagram where I’m trying to get intersect cases to return.
I concur with Ken, in that I can write a reasonably complex SQL statement on any of the above RDMSs and get a correct result (with minor tweaking for different function names and join syntax).
I wouldn’t dream of using anything else. Unless the something else could handle updating multiple fields in multiple tables in one statment. That’d be nice : )
I started “sqling” in SQL Server for about 3 years then switched to 2 years in Oracle due to professional reasons. I can provide testimonial that the “change” was only a matter of minor tweakings. It also provided me the acknowledgement that “problems” in SQL are not really in the SQL, but in the support the RDBMS provides to what you want to do.
You have the stored procedures thing if you feel the need to do something more programatically, but I can’t imagine how could we do queries with such… querying precision in a programming language like C# or VB.NET. I think that it is possible to implement, say, a .net class to do direct queries in tables, but I think it will just “simulate” the SQL concepts. If so, do we need to do more spaggethi?
While I am no SQL expert, I don’t mind the language at all and don’t understand the objections to it. I have worked with Assembler, COBOL, Focus, Natural, Java, Perl, C, Pascal, Basic, VB, VB.NET – not to mention utility, web, and OS languages. So I have a good basis for comparision.
Like mikeB, I appreciate a language that one can actually read. Save me from untangling regular expressions or trying to figure out what extension of a Java class someone has implemented (or if they created their own)! SQL has idiosyncracies that a programmer has to learn as do all languages. It can’t do things that some other languages can do. But these languages are tools – sometimes you use a hammer and sometimes you use a screw driver. SQL is one of my most frequently used tools – which I can’t say about Java or C or VB.
Jan Durbin |
I’ve done some complex queries in ACCESS. It helps enormously to be able to see the ACCESS-query in SQL as SQL spells-out boolean qualities such as AND and OR, etcetera, and then to make necessary corrections to the ACCESS-query from the displayed SQL relationships (I had what I thought was an OR but was really an AND, and therefore the function wouldn’t and couldn’t work. The human brain says ‘it is this and that’ but SQL and ACCESS-query don’t necessarily see it that way, and so the designer has to conform.
Chris A |
I think Noons makes a good point. Though I wouldn’t dismiss anyone who doesn’t know the difference as incompetent.
As a developer who is very interested in programming theory, I can recognize the difference between SQL and other languages without dismissing SQL as not being a real language, or even being a “bad” language, which many people want to do.
There is a big difference between SQL, which is decalarative (like Haskell, LISP, etc.), and languages such as C/C++, Java, VB, and other popular imperative languages. These imperative languages were designed to tell a machine very specifically _what to do_ and to a certain extent _how to do it_. SQL does not do this. It doesn’t micromanage the platform, or else it wouldn’t be able to be used on multiple database platforms with so little modification. SQL rather tells the database _what data it wants_ and allows the database to determine what needs to be done to retrieve that data. As a tool of abstraction, this aspect of SQL has its advantages and disadvantages.
This high level of abstraction is _great_ in many ways because it is really a Domain Specific Language (DSL), that allows a person to get real results with very little technical knowledge. If you know the data, you can get what you want. You don’t need to know Oracle, or SQL Server, or Sybase, or Windows, or Unix, or whatever. You just need to know the data, and a little SQL.
However, this high level of abstraction can be a real drawback when things get complex. Because in trade-off for the ease of use, you lose a lot of performance. A lot needs to happen under the covers, and a lot depends on the intelligence and sophistication of the engine that determines “how do I get this data efficiently”. When this engine falters, we end up with a “leaky abstraction”. Suddenly, the foibles of the platform show through the nice clean SQL interface. We start tweaking the query, trying to find a new way of describing the data we want that has the exact same meaning, but implies to the database a different method of retrieving it. This is not what SQL was designed to do, and unfortunately there is little recourse in many cases. In Oracle, we have the ability to use PL/SQL, with SQL Server there’s a simple scripting language (T-SQL, I think?), but there is often a performance trade-off here as well, and we lose the platform independence.
I do think that there are ways SQL could be extended to close at least some of these “leaks”, and I’ve toyed with possible ways of doing this… but I’m not a language designer, so I’m not sure of the best way. In fact I’m not totally convinced it should be done at all, if it meant we must lose the true declarative nature of the language, or the platform independence. In the end I think the answer lies in two parallel efforts: smarter planning engines, and better schema design. The system architecture shouldn’t be a hindrance to efficient retrieval. And the database engine should be able to determine enough about the data distributions to figure out an acceptably efficient method of retrieving the requested data. All without causing the SQL developer to go through as few contortions as possible.
At any rate, all of this is fairly abstract, and depends on a broad knowledge of programming theory, beyond just a thorough practical experience in programming. And this theoretical education is lacking, or at least scarce, in many university degree programs in computer science and engineering. Which is why I wouldn’t dismiss someone unfamiliar with the subtleties of imperative vs. declarative languages as incompetent. Though it is valuable knowledge that I think every developer should learn.
SQL is a way of life for me, I use it every day at work, and do not foresee any changes in the near future. I find it simplistic and extremely logical, even the more complex cases, and am adept at it on most DBMS platforms.
Note for Jan Durbin — If I have to use Access, then I write all the queries in SQL. Far easier to see what you are doing and what results you are likely to get.
Bill L |
I agree with much what has been said here in favor of Structured Query Language. Chris A. has covered well a considerable parcel of ground.
As I see it, the essential difference between SQL and other “developer” languages is SQL’s set theory orientation. For relational data retrieval and manipulation, I find it difficult to imagine a more perfect tool.
In response to Chris A-
I agree with some of your points, but not all.
“SQL rather tells the database _what data it wants_ and allows the database to determine what needs to be done to retrieve that data.” – This is not entirely true, SQL statements can also be used for action statements e.g. create, alter, insert, update etc. All these tell the database what to do, and although SQL doesn’t “micromanage the platform,” it can be used to micromanage the database.
“…allows the database to determine what needs to be done to retrieve that data.” This is not always the case. In Oracle’s SQL queries, hints can be used to override the way that Oracle handles and searches for the data.
“In the end I think the answer lies in two parallel efforts: smarter planning engines” Yes, and the DBMS suppliers have been making far better “planning” engines lately e.g. Oracle’s 10g. If the DBA keeps the relevant statistics up to date, the engine will calculate the method with the lowest amount of “work” and will use this “plan” to fetch the data. A question here would be: “Why does a DBA need to keep the statistics up to date, can the database not do this automatically?”
“and better schema design” Couldn’t agree more, but it is only wishful thinking.
“And the database engine should be able to determine enough about the data distributions to figure out an acceptably efficient method of retrieving the requested data.” They do do this in most cases.
Altogether, though, a nice response, and has some good points.
mahraj m |
I feel the same way as Brian Peasland expressed his thoughts. In the beginning of my career with Oracle database using SQL I used to feel like few blind people is trying to look at the elephant. Later on I have got better feeling using the sql and its logic. In one word I can say I like it. It gives the inside world of databases where we live most of the time. I think most of the DBAs/ Developers feel the same way. If not, they should learn and go with the flow.
What I like the MOST about SQL is I can take those questions from a business case and have pseudocode written so the customer can understand the English statement with no problem. Taking that pseudo-statement and transforming it is very simple, usually deleting the conjunctions and renaming their areas of interest to the tables and views representing them. SQL is so nice for customer by-off.
I think SQL should be considered as a “database access tool”. It is strictly a method to insert/retreive data to/from a database.
I remember Hierarchical DBMS where you had to recompiled the world every time a change was made to the database because SQL, or a form of it, did not exist.
I would like to find a book or site where I could learn how to better present the results of my queries without having to define a “package”.
Dave M |
I have been using a real Text database for years before Oracle and other SQL databases acquired this ability. Its unfortunate that SQL DBMSes acquired this ability as 1) its unsuitable in comparison to a real text database 2) SQL DBMSes are the only flavour that consultants and managers will consider, regardless of the performance hit and cost.
Try doing a query with several verbose columns with a select statement. Try doing a free text search and determine the “hits” on the various words in the statement. Neither of these are a problem with a real text database.
I absolutely LOVE writing sql! It’s like I’m a kid whose been given an enormous set of Tinker Toys — the more I play, the niftier the results! The addition of the Oracle CASE statement was like adding a motor to my toy set.
I relish the precision which can be succinctly stated with sql, and the whole concept of set processing makes beautiful sense to me. There’s a feeling of mastery that comes from knowing you can present data in almost any manner you can imagine.
andrew liversidge |
Heya – SQL is the greatest. So much power for so little effort. I have worked with some really great pro’s and it is wonderful that you can constantly come across new tricks – keeps the interest up! As for Sql Server vs Oracle – Oracle is great but not everyone can afford it. Sql Server fills an important niche! Love them both!
Jay Munshi |
SQL for a lot of developers makes their life very easy. Especially when they have a DBA around. I have seen some Java Cats who have been miserable at SQL but once they realise the power of letting the database do the fetching and manipulation they too realise that its a tool that they cant live without when it comes to some really complex joins. Easier to join tables in SQL than calling two tables in java and joining. Trust me I have seen that happening more than once.
Love it, hate it but you definately cant ignore it.
I love SQL and I hate it. But the elegance of SQL is highly dependant on DBAs and application designers. They could help us a lot by choosing names carefully. With clear naming of columns and tables most SQL will be quite readable.
What I hate most in SQL is the group by clause, which is obligatory but totally meaningless unless in subqueries.
What I love most is that SQL let me use masked data and let applications work on complete ‘files’ which are actually queries.
BTW: Something I never understood is why SQL is called Structured.
SQL and PL/SQL, is so powerful. How anyone that works with any data can sat anything else, is beyond me. I am by now means a PL/SQL programmer, but as an Apps DBA, the more your work with it, the more pwerful it becomes. Be Patient!
Mark Jacobs |
I love SQL. It’s ability to manipulate great amounts of data in single — if at times complex — statements is without parallel. And yet when you think about it, it’s complexity is really built from very simple operations: state what you want, where it resides, how to combine it and how to summarize it (selection, projection, join and aggregate). It does require that you think in sets and not procedurally, but that’s a great strength not a weakness.
Add to that the many means of optimization, implicit and explicit, and it’s a keeper. And I’m hard pressed to think of another language that allows you to restructure the data in the same step as retrieval.
The only real quibble I have is that all of the SQL engines I use *still* have order dependent parsers. The SELECT clause must come first, then the FROM clause… Seems like a silly restriction.
Steve Luckcock |
It’s horses for courses. SQL is designed for reporting on and maintaining information held in relational databases. It does that job using a very simple declarative syntax that is considerably easier to learn than the majority of development languages. I have known many otherwise technically niave computer users who have understood and used SQL in its various guises with relative ease who would have run a mile rather than approach ‘difficult languages’ with their variables and loops. Obviously you couldn’t write an all singing all dancing graphical application in it, but that’s not its purpose. I don’t know of any easier or more powerfull database language.
Chris A |
Regarding insert, delete, and update. Yes, these do tell the database to do something other than retrieve data. However, like selecting, they don’t tell _how_, just what. Also, the amount of SQL’s syntax dedicated to SELECT alone dwarfs that of UPDATE, INSERT, and DELETE all put together, which the main reason I didn’t mention them.
Regarding ALTER, CREATE, DROP, and other DDL portions of SQL, these also don’t have to deal with retrieving data. But they are definitely declarative in nature, just as SELECT, UPDATE, INSERT, and DELETE all are. However, they are also often subject to many more platform-specific modifications and extensions than the DML portions (INSERT, SELECT, etc.) are.
And speaking of platform-specific extensions, this is what Oracle hints are. They aren’t part of SQL as a generalized language. Which is why I would say they are far from an ideal solution.
Chris A |
Forgot to mention….
Thanks, sqlsgreat, for the complement, and for pointing out the holes in my post.
Dmitry A. Tikhomirov |
I love SQL beyond any doubts. The abilities of SQL are very large. As any language SQL has some discomfort features but it’s nothing in comparison with SQL power. Fundamental SQL understanding gives me an unlimited potential for tasks solving.
Aamir Abbas |
Well… I think, everybody is saying correct (as of their mindset with the subject).
In my opinion, intentions to compare SQL with other languages are not valid because these are developed/evolved focusing on different goals…
C/C++/Java/VB/C# and the couterparts are made to do general purpose (non-specific) programming. One may be developing a business (database-oriented) application while the other may be creating a 3D game…
SQL, on the other hand, has a very clear goal. It is meant to be used to play with data in a database. It is never used (alone) to do the stuff like the former.
SQL is as beautiful as C/C++/Java are. SQL is beautiful enough that it is used in 99.9% of business applications.
So, well, I am a fan of SQL while also do VB/Java etc. They all have their own place.
With all my respect for the commentaries above, I am astound to notice that a “DBA and expert in database design as well as backup and recovery” (Brian Peasland) makes such a confusion between the relational algebras operators. At this level you are not allowed to say that “A ‘selection’ is a vertical slicing of the grid”. Maybe this will help you: http://infolab.stanford.edu/~ullman/fcdb/slides/slides5.pdf (read only the first page before turning to Ullman’s book).
I am sorry for this, but my students consult this webpage and they deserve to learn SQL so that they will still be able to use it even if the French decide to translate the SELECT command using the French language topology .
I was new to Oracle and D2K reports when i joined my present org, but believe me SQL works with all those having sense and ability to visualise the result they want from their query! I never ever touched any book for SQL and learned a lot on my own, watever one wants to dig out from table(s), frame query in simple english and replace the functions later with SQL inbuilts if needed and voila! I just luv it!!!
Norman M |
I recently took a course on XML and the various “standards” for accessing data within XML documents. It really brought home to me what a masterpiece of concision and readability SQL is! Admittedly there are minor variations in syntax between different implementations such as Oracle and SQL Server, but these are nothing in comparison to the different flavours of committee designed, cryptic gobbledegook built into the XML access methods.
SQL is my sweet thinking language.Being a DBA my imaginations are finding its desire on DBMS through this. No other language is this easy to talk with your database, even can drop the database with this SQL.
My hatsoff beloved votes to my beloved SQL. I hate who hates it..
SQL as implemented by the major vendors is a language based upon sets. Procedural extensions (IF/ELSE, LOOPing and such) allow you to step through sets. Problems usually begin when folks do not understand set-based questions, detail vs. aggregate, and how to say it in SQL.
I agree somewhat with Noons who used the phrase “development” language referring to C++, Java and such. Those are the types of languages used to call well-designed and properly coded PL/SQL packages, bursting with SQL goodness!
SQL: The “set theory” approach that it relies upon is fine i.e. it works, but complex inline views, which involve nested selects are nowhere near as easy to understand “at a glance”, or see on the page, as procedural code.
I have been coding SQL for about 15 years, starting with DB2, then Oracle and now with Teradata. I have always liked SQL. But I am from the old school where you had to code in an unfriendly support product using SPUFI and ISPF for DB2 or vi and SQLPLUS for Oracle. I am on a project where I have been asked to code several PL/SQL stored procedures…. now that is where I get really frustrated with syntax, since I am not a developer by trade. I found out that Oracle has a really worthwhile free development tool that you can use to code PL/SQL, called SQL Developer. Now I am flying through the syntax hurdles and this software has actually inspired me to see how many new features I can incorporate into the design…. The software is available on the oracle otn site. I just downloaded a new version today and it is working even betten and the productivity gain is unreal….due to the interactive debuger that points out the compilation errors without the repeativle cycle of typing “show errors” in a SQLPLUS session to get a clean compile.
I’ve been using Oracle and SQL for 20 years. It’s definitely gotten more powerful over the years and there are very few situations where I can’t get the data out that I want using just straight SQL. It was meant to “manipulate data” as previously stated, not that the place of a complete programming language. Now as far as Chris Date goes, he hasn’t been a fan of SQL for as long as I’ve known. SQL serves me great right now. If someone can miraculously come up with something better, bring it on.
Phil Singer |
Trust me, Richard, with a few months experience, complex SQL statments become much easier to understnad “at a glance” than procedural code (especially that kind of cryptic C code which relies on side effects to do it’s job in a minimum of keystrokes. You coders know who you are).
Especially after you have been away from the application for a few months.
I concede that the advantages are not as great if you are dealing with a poorly designed application, which is just a redesign of a punched card system with the SQL doing nothing more than replace READ/WRITE statements.
A comment on Richard – “complex inline views, which involve nested selects are nowhere near as easy to understand “at a glance”, or see on the page, as procedural code.”
Of course “select a.tablefield, b.tablefield,c.tablefield from address a, branch b, costcentre c where a.idn= b.idn and c.idn = b.idn and a.cat=’ACCESS’” is always going to be harder to read if you you a lax on providing an effective way to read SQL.
It is really a matter of which method you take “quick and dirty” or “structured” with a view to someone else reading your code.
SQL supports both, but you have to choose which you are going to do.
I’m very happy not having to learn another development language for my work which is almost entirely about database integration.
I don’t have to write code, just scripts and then have the external application execute the script. For that SQL suits me fine.
Jim P |
Why would anyone look at something with such criticism and at a microscpoic level ? Why make things harder than what they have to be ? Looking so deeply into something to find fault in it is not only a waste of time but a self defeating animga. That was alot of big fancy words in your first post, but you said absolutely nothing.
SQL is a tremendous achivement. Its fast and simple. What else do you need ? There are no “leaks” and its not slow. If it is slow then perhaps it isnt SQL that is the problem, but the database design. Databases should be designed so that the queries used against it are fast and easy. Proper DB design along with SQL is the answer. People should not make things so difficult or tedious. Databases should not be made to be so difficult. all they do is hold records, period. I mean really, what does one really need to do ? Add a record. Delete a record. Edit a record (update) and find matches to a criteria. Thats it. And it should not be made anymore difficult than that. I often wonder if some programmers, trying to save a millesecond on a query, would spend an additional hour or two making a SQL statement so complex that they cant’t read it themselves the next day. Maybe 2 queries need to be performed instead of one, saving an hour of coding time and maybe taking a second or two more to execute. Easier is far better than complex. I keep it simple. I got a second to spare.
Time to go fishing!
Chris A |
I’m sorry if I come off as condescending. It’s not my intention. It’s just that SQL is so universal, used by so many people. And few think of the possibilities, or problems, that arise outside their own particular usage scenarios.
As someone who has worked in databases both large _and_ small, and as both a database _and_ an app developer, I was merely trying to bring a different viewpoint to the conversation. I can say with some confidence that I have a wider view of the merits and blemishes of SQL than some others do. Though not as wide as many others, such as probably our hosts here at TechTarget.com.
To answer your question though, one looks with such criticism at a microscopic level when it becomes necessary to do so in order to _get things done_.
Things are not always so simple, whether we like them to be or not, when dealing with large amounts of data, or strict processing time limits. I am at the _small end_ of the “large dataset” category, dealing with 10-year-history tables holding several million records. Small inefficiencies when dealing with this many rows can amount to _hours_ of difference in batch processing times. At a conference last spring, I encountered someone who was importing hundreds of millions of new records on a _nightly_ basis, pushing Oracle to its very limits.
“Simple” and “large” are very relative terms. You are fortunate if you are able to keep your SQL simple without having to worry over performance. But we should all recognize that not everyone is in the same situation. Everyone has a different set of obstacles, and everyone deserves to get the help they need in overcoming them. It does no one any good to dismiss others’ obstacles as unnecessary or unimportant. Regardless of whether that person is a veteran or a newbie.
Zaffer Khan |
Seems interesting to browse through different views on SQL and its user. SQL & PL/SQL make Data access and manipulation more interesting and easier.
With exposure to both Development and Administration of Oracle Database, I can’t picture how tough it would have been if there wouldn’t have been SQL, PL/SQL.
Thanks & Regards,
I LOVE IT!
I think it just depends on where you come from!
I started “database” programming (developing applications that use databases) using DBase and Clipper (does anybody remember?), and I remember spending hours and hours developing routines to handle data, that can be solved in few minutes using SQL.
I’m not going back there.
May be there is something else to come, better, but from my point of view SQL is heaven!