Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

closed as primarily opinion-based by gnat, Bart van Ingen Schenau, MichaelT, Kilian Foth, ChrisF Aug 7 '13 at 11:47

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

14  
I feel old. SQL has been abstracted far enough that you can now seriously ask this question. –  Mark Aug 14 '11 at 2:52
11  
@Mark: Perhaps you can seriously ask the question, but the answer's still the same. –  Adam Robinson Aug 14 '11 at 4:00
28  
is knowing arithmetic still important if I can use a calculator? –  Steven A. Lowe Aug 14 '11 at 8:09
4  
NHibernate without knowledge of SQL Profiler and how to tickle its belly to use JOINs, is a performance jihad waiting to happen for your database server –  Chris S Aug 14 '11 at 12:37
2  
Do you have to know HTML if you can use Dreamweaver ? –  user61852 Aug 6 '13 at 1:51
show 4 more comments

14 Answers

up vote 60 down vote accepted

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:

  • Recursive and/or hierarchy queries
  • Optional parameters (particularly translating them into range predicates)
  • User-defined data types
  • Platform-specific types (SQL hierarchyid and TVPs, Oracle arrays and nested tables, etc.)
  • Batch inserts/updates/upserts/deletes
  • Index hints
  • Lock hints (especially update locks and dirty reads)
  • Error handling
  • Outer joins - their result sets map poorly to the OOP model, many ORMs have their own query language but that is similar to knowing SQL itself;
  • Modularization through stored procedure and UDFs, especially inline UDFs and CROSS APPLY queries
  • Use of OUTPUT/RETURNING for staging data into multiple tables
  • Efficient paging queries
  • Queries based on windowing functions (rownum, rank, partitions)

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.

share|improve this answer
    
I agree with most of your points, but regarding outer joins: yes, they map poorly into OOP, but I think the OOP equivalent (e.g. GroupJoin in LINQ) is much better. –  svick Aug 14 '11 at 13:28
    
@svick: It has its uses, but it's not the same thing. Try emulating a full outer join with GroupJoin. –  Aaronaught Aug 14 '11 at 17:19
    
Yes, it's not the same thing. And I think what you need most of the time is actually a 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. –  svick Aug 14 '11 at 17:26
    
@svick: Thanks for the tip. Hate to pull rank but after a year-long hiatus I'm still one of the top contributors for both SQL and Linq on Stack Overflow, so I'm pretty sure I understand the difference in approach. Full joins are rare, but sometimes they're actually what you need and there's simply no analogue in Linq. It's quite messy and inefficient to get the same result set, regardless of how it is structured. –  Aaronaught Aug 14 '11 at 17:37
    
It seems we actually agree. In most cases, you don't actually want outer join. But when you do, it is hard to translate it into LINQ. –  svick Aug 14 '11 at 17:43
add comment

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". ;-)

share|improve this answer
add comment

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.

share|improve this answer
3  
I also think that sometimes (even with regular SQL) people forget that just because it returned a data set didn't mean it was the correct data set. I think this becomes easier to forget with an ORM when you assume it did the correct thing as you don't really understand what it did anyway. –  HLGEM Aug 15 '11 at 18:25
    
I disagree on ORM being less complex than SQL. With SQL you can retrieve data without programming. SQL is not a programming language but a declarative one, so it has no while, for or if-then structures. –  user61852 Aug 6 '13 at 1:47
    
A declarative programming language, is still a programming language. SQL is a special purpose programming language, and yes it is declarative for the biggest part. As for the meat of your comment, I don't get it. SQL while not a general purpose programming language, is still a language. You still need to know the syntax, its limitations and flaws. –  Charalambos Paschalides Aug 7 '13 at 18:41
add comment

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.

share|improve this answer
3  
+1 for leaky abstraction reference –  tdammers Aug 14 '11 at 8:18
add comment

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.

share|improve this answer
add comment

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".

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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!

share|improve this answer
2  
I agree: if you don't know SQL, you often write dozens of lines of code to do what you could have done with a single SQL query. –  benzado Aug 14 '11 at 15:56
2  
re "learn it the first time you need it": ro-che.info/ccc/11.html –  MatrixFrog Aug 15 '11 at 16:25
add comment

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.

share|improve this answer
    
While I agree that knowing SQL is very useful - basically mandatory - I disagree with your reasons. With that reasoning you have to know ASM and your CPU architecture before writing any program, because high level languages makes things easier but they are tools (to abstract things), so if you refuse to learn your processor instructions and architecture you have no business using a computer. <--- Makes no sense. The real reason you need it is that ORM tools are still in their infancy; I wouldn't be surprised if 5-10 years from now SQL knowledge ceases being needed –  Andreas Bonini Aug 14 '11 at 11:27
    
High-level languages are built in such a way that they prevent you from having to know about the underlying architecture, true. That's possible because the domain is commensurable with the underlying architecture. ORMs, however, are a different matter. I'm a huge fan of ORMs, but I don't think a day will ever come when you can use one without knowing SQL (or whatever the underlying DB uses). The object and relational models are fundamentally incommensurable, and I think there will always be concepts that don't translate from one to the other. Besides, I'm talking about today's ORMs, not future –  Marnen Laibow-Koser Aug 14 '11 at 15:23
3  
+1: "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." –  Vector Aug 15 '11 at 3:13
2  
I would upvote this a million times if I could. ther are way too many people who have no business touching SQl databases due to their general incomptence and lack of understanding of waht they are doing. They casue havok whereever they go creating performanc enightmares and writing reports (that actual business decisions are made from) that have teh worng results without ever noticing becuae they are willfully ignorant of SQl as if it is some sort of badge of honor to distain the database that is critical to their applications. –  HLGEM Aug 15 '11 at 18:31
1  
+1 for "tools (to abstract things you know), not crutches." –  sholsinger Aug 16 '11 at 18:21
add comment

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.

share|improve this answer
1  
Anyone who knows how LINQ works will be able to learn basic SQL in a day. This is a terrible argument for learning SQL. –  Boris Yankov Aug 15 '11 at 7:57
add comment

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.

share|improve this answer
    
Who needs to interact with the database ONLY through the application he/she is building ? –  user61852 Aug 6 '13 at 1:48
add comment

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.

share|improve this answer
6  
Indeed. You have to know for example the impact of different join statements and how it affects the performance. –  Chiron Aug 13 '11 at 23:05
14  
+1 No free lunch! What's with all the questions basically asking if ignorance is OK? –  benzado Aug 14 '11 at 15:48
6  
@benzado: Not that I think it's warranted for SQL, but you have to choose ignorance of some things; there's way too many technologies (even good ones!) to really know them all. So I think it's OK to ask "is X unnecessary if I know Y really well or if I'm doing Z?" –  Craig Walker Aug 14 '11 at 16:06
2  
@Craig I agree there's too much out there to learn, but a programmer really ought to have a basic knowledge of the levels below where he is working. –  benzado Aug 15 '11 at 15:29
2  
@Craig Walker databases are critical knowldge for most business applications nota nice to have. –  HLGEM Aug 15 '11 at 18:13
show 3 more comments

Not the answer you're looking for? Browse other questions tagged or ask your own question.