I don't have any serious experience in SQL and I even hate to write SQL instead of LINQ. I am happy enough with ORMs.
From the employers and sector view point, is it important to know SQL? Do I have to master on it? Are companies that prefer pure SQL over ORM frameworks a "dinosaur" in the programming world?
This is hard to explain to a lot of programmers, because if you only know basic SQL then it really doesn't give you much of an advantage over the crutch of an ORM. The more advanced SQL concepts, however, are a crucial part of the difference between applications that just work vs. applications that are high quality (in particular, fast and reliable).
I'm assuming somebody else designs the databases for you, because doing that without knowing any SQL is just beyond the pale. But even if you're only developing against them, here is just a partial list of all the things that ORMs still tend to do poorly or not at all:
The list goes on and on - a lot of these are things that a novice DBA has never had to do, and a novice developer has never even heard of - but they are very important in larger-scale apps.
ORMs are really great at speeding up the really boring SQL code - that is, all the repetitive CRUD and mapping and other plumbing code, so there is absolutely no shame in using them and don't listen to anybody who says they're evil. But you definitely still need to learn and yes, even master SQL, and be prepared to drop down into raw DB commands/queries when the ORM isn't pulling its weight.
GroupJoin in LINQ) is much better.
GroupJoin.
Commented
Aug 14, 2011 at 17:19
GroupJoin. It's just that people who are used to SQL immediately think of outer join in those cases and then ask how to translate that into LINQ. That's not the right approach. You shouldn't try to translate your SQL into LINQ, you should translate what you need directly.
Absolutely! SQL is still the lingua franca of databases and although you may do a lot with ORMs you have to understand SQL to understand the decisions ORMs make and the SQL they generate. Also, there are still lots of things that you have to do with custom sql and stored procedures as well. Sorry, no free lunch.
Yes, you still need to know SQL. ORMs are a very leaky abstraction, and do not provide access to the full power of SQL. For toy applications you may be ok with limited SQL knowledge. For enterprise applications, you will have to understand the database to get decent performance from the ORM. Also there are a great many tasks that are much more easily accomplished with SQL than with an application language. I have seen Java programmers spend days writing code to do something that could have been coded in SQL in an hour. SQL knowledge is very valuable to anyone writing applications that use a relational database.
SQL skill is a must have skill in IT today. LINQ is a Microsoft Only technology. SQL usages go beyond web and client/server application development. You can't model databases and do ETL if you are not good with SQL. You may not need to master SQL dialects used in ORACLE and SQL Server for their Data Warehous products, but you should know standard SQL. SQL basics are simple, there are tons of material to get you started.
If you're interacting with an SQL database, you must understand the SQL that the ORM is generating. You must understand the concepts inherent in SQL databases, and you must understand what your ORM cannot do for you. ORMs make life easier (and yes, I think working without one qualifies you as a dinosaur in many cases), but they're tools (to abstract things you know), not crutches (to prevent you from learning).
If you refuse to learn SQL, then you have no business whatsoever touching SQL databases, either with or without an ORM, and you should find another type of work.
I will admit that I am a die hard ORM fan and have been preaching the benefits of ORM for years and had a lot to say about why ORM trumps SQL.
BUT...
I have to admit SQL is totally and utterly required in the real world and every developer should have a good understanding of SQL.
SQL procs are faster than the ORM in almost all cases. Even though you can optimistic the ORM output in most cases too make it come in a close second to SQL procs.
On occasion, you require some heavy duty SQL to get the result you require, and these monster queries are easier and faster to create in SQL procs.
Just my two bits.
There will come a time when you have to optimize something complex that the ORM is creating. At that point you generally have an extremely complex query to break down. If you never learned basics, how can you expect to start learning SQL with the advanced stuff? You won't understand enough to even start. ORMs in the hands of a person who understands SQL - a good tool. ORMs in the hands of someone who deosn't know SQL at all - disaster waiting to happen.
One of the reasons why SQL is critical to understanding databases is that application programmers don't naturally think in terms of data sets. But the only way databases operate at all efficiently is by sets. You need to learn how to think in sets before you even attempt to use an ORM.
I find myself manually forcing queries in ORM frameworks more often than not. And while most have their own query language, those are all inspired by sql, the code gets turned into sql, and you need to understand what's going wrong by reading that sql when (not if, when) things go wrong or perform poorly.
So even if you're not writing sql directly, understanding it beyond a basic level (though you probably won't need to learn things about writing stored procedures, triggers, etc. if all you're going to do is access databases) you should know the language enough to understand the generated code and tweak it as needed.
Before I talk about the question, a little introduction first; I love ORMs. And I hate SQL. I love ORMs because they hide the user-unfriendly mess that SQL is, and provide a nice language-integrated way of dealing with the database.
However I gotta admit that SQL has a lot of benefits, with the bigger one being precision. I can pretty much argue about the complexity of an SQL query, without detailed knowledge of the underlying database schema, just by going through the query. Therefore, when I use SQL, I am always alert, and I always have an intuition about where the complexity of a component is heading.
On the other hand, when using ORMs, I'm so overwhelmed by the ease of database integration, that I literally forget there even is a database. This has screwed me multiple times. Many times in the past, I have called innocent-looking ORM methods, that behind the scenes call massive and scary database joins, that destroy performance.
That's why for me, the truth is somewhere in between. I love using ORMs, and I will continue doing so, but I gotta be more careful and always study the implications (on SQL level) of any ORM method call. Knowing the implications of an abstraction layer is pure gold in this business, and justify the use of the abstraction. Anything else, is like shooting yourself on the foot.
If all you're ever required to do is interact with the database only though the application you are building, you probably don't need it. At some smaller companies or developer teams, you may have to do some support. It's a lot easier to connect to a client's database and run a few sql statements to see what is going on with their data.
Even with a good, mature ORM you are inevitably going to run into situations where its emitting some inefficient SQL and its going to make your life a lot easier if you can grok what's going on in the generated SQL. That said, if you're very proficient with the ORM and know how to use a profiler for your DB of choice you could pretty easily "fake it til you make it".
The answer to all these types of questions ("do I need to know X?") is "learn it the first time you need it, if you'll ever need it".
It's important though to not fall into the trap of doing things less efficiently because you are not aware of or are not willing to learn the efficient way to do them.
In this particular case, for example, what do you do if you realize that there was a bug in your program that caused the PostCount field of your user table to sometimes be inaccurate.
You fix the bug and now you have to update the PostCount for all users. How do you do it?
If you write a little script using ORM to do this then you are being very inefficient; a very simple SQL query will do.
Since these situations are fairly common I fear that you fell into the trap described above!
Yes, you still need SQL.
Do not jump to the assumption that something "old" is somehow unworthy of consideration. So called "legacy" technologies are those that are still around after having stood the test of time. We don't call Wang computers "legacy", they failed, and are gone.
If you ask me does it bother me that my bank is probably using COBOL on a mainframe, or IBM i? Absolutley not. These are rock solid, tried and true technologies. Guess what, they are mostly using DB2 SQL. I'm much happier trusting my money there, than in software developed in a fashionable language of the month.
The dinosaurs lasted on this earth much longer than we humans have been around. And if you read Jurasic Park (yes, books are better than movies) then I ask you, do you really want to face a pack of hyper-clever velociraptors, or a dogged T-Rex that never gives up? Don't get bitten by underestimating the "dinosaurs". ;-)
Apart from games and (mainly statistical) research, which I have no experience with, it seems that database access and operations are one of the few areas where wrong decisions lead to very large performance hit. I'm a strong believer in more powerful database abstractions in code, and I believe linq, which ties the database operations to the programming language, giving you all the tools of the language (type checking, syntax checking, all the things you like about your programming language) while still giving you plenty of power to do what you want is absolutely terrific. Unfortunately, it still doesn't always work. That means, if the abstraction layer gets its optimisations wrong, and you might be waiting for seconds instead of microseconds, or minutes instead of seconds for your result. Since these are very noticeable times, you have to optimise them away, or your application doesn't "work": performance becomes the greatest problem of your application. That means you have to get closer to the metal, and hand optimise.
When we get to the point that manipulating large datasets takes for example 3 ms when doing by hand, and 100 ms when you let the abstraction layer do it for you, by all means, let the abstraction layer handle it for you, because you might be 30 times slower, but you are still (probably, for most applications) fast enough. The reality of the situation is however that when you are looking at hand optimised solutions where you have a 200 ms response time, and when the abstraction layer handles it for you, the algorithm takes 'just' a 10 times performence hit, you have a 2 second delay, and then you care a whole lot, as it goes from not all that fast, to painfully slow. Seeing that relational database solutions don't scale well, I don't think this will be solved in two or three years. This means you will still have to get down to the bare metal for quite some time when it comes to larger databases, or your application will be so slow, it can't stand up to competitors.
