Data Management

Poll: How much SQL do you write?

A long running argument in the development community is how much SQL code developers should be writing. Tell us whether you're writing a lot of SQL.

One of the long running arguments in the development community is how much SQL code developers should be writing, as opposed to letting a DBA handle it. In addition, many tools now reduce or even eliminate the amount of SQL that developers write. And, there are some programmers who do not use SQL at all.

I've had jobs where being a "developer" simply meant "put a nice UI on top of a ton of SQL you write," and I've had jobs where I didn't go anywhere near a database. Tell us whether you're writing a lot of SQL in your current job.

J.Ja

About

Justin James is the Lead Architect for Conigent.

70 comments
EPIKService
EPIKService

While I totally appreciate the work and workload of the DBAs' in my organization, if I were to ask a DBA to write SQL for me, the most I could expect would be a chuckle!

eftpotrm
eftpotrm

I'll stress that if I was doing the normal company way at my place, I'd write very little SQL and have to wait weeks for anything to happen while the DBAs decided I didn't know what I'm doing. Rubbish. I know my databases, I'm just as good - perhaps better - at SQL as I am at .Net, and I write my apps to use both to the best of my abilities. Which is why I'm glad I'm currently on a semi-skunk works project and can 'prototype' with full control to my heart's content :-)

Kruger.henning
Kruger.henning

Lots of SQL, table creation, stored process, triggers and packages including optimization (Part of our Development DBA role which all developers here must have). It is about 20-30% of the development process. The rest is GIU/server backend/web/warehouse coding.

gypkap
gypkap

If it's a database application, quite a lot of SQL. I've often mixed VBA with Access SQL or SQL Server when needed.

lakario
lakario

I've become spoiled by the Entity Framework, which keeps the SQL code out of my hands and leaves me writing elegant LINQ expressions. When I do use an ODBC-type driver I find I am writing all of the SQL myself, but that's probably because my employer doesn't have a DBA.

CodeCurmudgeon
CodeCurmudgeon

I checked a lot 'cause pretty much all the SQL for a system is written by the application developer who works from business requirements through finished product in the hands of the user. We create the tables, write the views, triggers, stored procedures and we build the user interface. The DBAs are pretty much concerned with maintaining the database & app server software, creating new instances of the database and on the rare occasions when one of us has a performance problem, helping us sort that out. Joe Celko's SQL for Smarties has helped me solve way more problems for me than talking to the DBAs ever has.

doug
doug

If our company had a dba on staff, I'd be doing much less SQL - and it would be better written as well, but you have to make due with the situation you have.

clownfish
clownfish

As much as it takes. I like doing everything but the trivial in the DB, specifically PL/SQL. It just works really well if you have business rules that are data intensive. But I have a prejudice because I like to think in sets.

oldbaritone
oldbaritone

How much SQL do I write myself? Not much; the code generators take care of most of it. Pareto's law. 80% of the code uses 20% of the work. But, like the HTML code generators, SQL code generators aren't perfect. A little clean-up afterwards makes the machine-generated code more efficient, or adds some functionality that's beyond the capability of the code gen. That's where I do the work - the "20%" Auto-coders do the mundane work, then a "real progrmamer" puts on the finishing touches.

e.knop
e.knop

Nothing wrong with writing a lot of SQL. I personally don't believe in tools that do the SQL part for you. But the question should be, should everybody write SQL? And I think the answer is no. Bad SQL can ruin your performance and impact the database as a whole. Writing good and performant SQL is an art in itself. That's what a lot of developers don't get and is probably why a lot of DBA's will close the door. So, if you're not good at it, leave it to somebody who knows what he's doing.

gary.hewett
gary.hewett

I write code that writes SQL - I also tend to write code that writes code...

elitesky
elitesky

I write a lot of SQL code. For me, almost every dynamic application or website involves a great deal of SQL. Esp if it includes Ajax. There's just no way I would not write SQL, otherwise it would be static

apotheon
apotheon

I selected "very little" because, while I don't even write that much, I don't think "none" is strictly accurate. I haven't written a lick of SQL since November or December 2008, though. The real answer for me would probably be "as little as I can get away with". I prefer well-designed languages. Even CJ Date, one of the premier relational theorists in the world ("relational theory" is what forms the basis for SQL-based DBMSes, for any readers who aren't aware of this), who worked with Edgar F. Codd at IBM on the development of relational theory when it was first invented, thinks SQL itself is hopelessly broken. He writes craploads about how to work around SQL's limitations when trying to work with data in a relational manner, not because he thinks SQL is a good idea, but because SQL has become so prevalent and entrenched that one cannot reasonably avoid it if dealing directly with modern RDBMSes. I'm inclined to agree with his assessment of SQL (though with a much more superficial understanding of the problems than CJ Date's), which is part of the reason I try to avoid dealing directly with RDBMSes.

mattohare
mattohare

I really have to laugh at all the developers that think a ?DBA? has to write the structured query language code. I see it as a layer of the development process, even to the point where I will think of stored procedures and functions as methods in the database. Triggers I see as events. If I want my application to have the right data, then I?ll learn the database and get it myself. More to the point, I?ll organise the database myself. A DBA?s role would be to ensure that the database, and its server, run well. The DBA and I would have to discuss what it takes to ensure this, and the DBA would maintain it with additional code to do the maintenance tasks. Really, this division of labour gets to the point of classism sometimes. LOL

merakli
merakli

I can't think of a developer who doesn't write at least some SQL. For me a software developer is someone who "develops" something. That means, he/she has to be involved in all phases of the development lifecycle, from analysis through database design to UI and SQL for things in between.

Slayer_
Slayer_

I mean, my first job was to write a conversion script, 800,000 lines of SQL code later, I was done. Now however, i write very little. Often it's just. My manager walks up and ask "How many insurane accounts do they have in pending?". I just fire up query analyzer and type in a quick select statement.

Tony Hopkinson
Tony Hopkinson

devloper. That means everything I can do on the database server I will.

mattohare
mattohare

They're great at keeping the servers right. But if someone is going to write SQL for an application, it has to be an application developer. Even if it is one that specialises in database design and querying.

Tony Hopkinson
Tony Hopkinson

Real programmers write it all from scratch with edit.com. :D Never been keen on autocoders, more like 20 / 80 when I've used them. More trouble than they are worth. A decent editor plugged into the database schema, does me every time.

Realvdude
Realvdude

be they tools or entry level programmers. Understanding SQL is important, so that working code can become good/efficient code.

apotheon
apotheon

I personally don't believe in tools that do the SQL part for you. My take is that the approach a tool takes to writing code for you determines whether using that tool is a good idea. Tools meant to make things easy enough so that you don't have to know what you're doing in order to get it done -- tools that just hide how the SQL actually works -- are bound to introduce problems in the implementations of your database driven applications. Tools that are meant to be used by people who understand what's under the hood, though, and simply map the SQL to a model that works better with the program, that automates boilerplate management (e.g. working around SQL's many NULL problems), or that raises the level of abstraction in a safe and configurable manner so that tedium is abstracted away and one can do more heavy lifting with less work, are good things to have.

apotheon
apotheon

That's the way it's meant to be done -- when you can do so without introducing "leaks" into your system's ability to map a model onto your needs, raising the level of abstraction lets you get more done with less tedium. If it's tedious, it should be automated (which, amusingly, probably covers 98% of Java code).

jhoward
jhoward

Most of the work I do entails reading and writing some sort of information to/from a database. Since our internal requirements are constantly changing I end up touching this on a daily basis.

TommCatt
TommCatt

While I don't think it is a good idea for DBAs to be writing SQL (the DML part, anyway), I also don't think it is a good idea for application developers to write it either. I can generally recognize SQL written by application programmers who know SQL but who consider it just another computer language. This is not a claim that SQL programmers are better than app programmers, it's just that there are two mind sets, two ways of looking at data manipulation, that are involved. App programmers tend to write SQL that manipulates data in the same sequential/iterative way that is most efficient in Java, C++, C# or whatever. Good SQL code requires a set-based way of looking at data that is unnecessary at the app level. Good developers who have worked in both environments may be able to write good code at both levels, but it is a strain if they are attempting to do both at the same time. As for the app developer organizing (modeling) the database: not NO but HELL NO! This is a separate skill set entirely. Even a very good database developer is not necessarily a good data modeler. Which will probably be the better building -- one designed and built by a good architect, one designed and build by a good carpenter, or one designed by a good architect and built by a good carpenter? Division of labor may create a certain amount of elitism -- but it is natural for people to take pride in their job and this can easily spill over into a "my job is more important than your job" competitiveness. That's not an argument against division of labor, especially when that division is so objectively necessary. I guess I shouldn't complain. I make a good living cleaning up SQL written by app developers and redesigning databases designed by...well, apparently just about anyone who knows the format of a CREATE TABLE statement. Still, it would be nice if I could spend more time in development mode than repair mode.

ddouglas
ddouglas

I agree completely with what you are saying. In addition I'd like to add that as OR/M tools have become much better over the last 5 years less, less SQL is required at the DBMS level. Views are still a big thing, but in 80% of the cases, the view can be created quickly and efectively with a designer UI tool. I touch SQL the most when I need to create/update some complex stored procedures that require the performance of a stored procedure or have logic too complex for a view. I also touch SQL when modifying some of our legacy applications where we used to have a Stored Procedure get (sometimes multiple) and save method for every single table.. *Shrug* - glad those days of redundant SP's are over!

Tony Hopkinson
Tony Hopkinson

write a heck of a lot more. Too many starting with desktop databases, and developing bad habits if you ask me.

Duke E Love
Duke E Love

Between code generators, database abstraction libraries and ORMs I hardly write any SQL except for queries with JOINs or UNIONs and even a good portion of that is is handled by the data layer. The only time I really spend writing queries is for reports when I need to tease out a very specific data sets. Almost all of my CRUD is wrapped up in a data layer and my apps don't know or even care where the data comes from. I could swap out my data source for another database or XML or a web service and my app wouldn't know the difference.

apotheon
apotheon

I hardly ever write any SQL at all. SQL is actually not needed for a lot of development work. How much do you think people need SQL for device driver development, for example? Depending on how much you need to interact with an SQL database and how routine the interactions are, one might also be able to do everything using an ORM that masks all that fugly SQL by mapping it to a programming language's syntax. . . . and, finally, there are other types of databases in the world than those that are only (or at least primarily) accessed via an SQL RDBMS. For instance, I've been working with YAML databases on projects that have deal with small datasets -- too small and structured to even bother with the massive overhead of a traditional SQL RDBMS. While it's probably true that the majority of professional "software developers" write basic CRUD applications of various sorts, many also work in other well-defined niches, and some even work in very poorly defined fields with really interesting and innovative stuff that violates all the working expectations of the common daycoder. That last class of developers are probably the programmers who have the most fun with their jobs, too, where they aren't just learning new skills all the time but inventing new skills to solve problems nobody has solved before (or solving previously "solved" problems in a better way). edit: typo

Shellbot
Shellbot

.. being an actual client server database..hehee sorry.. just read the title and had to laugh :D

clownfish
clownfish

... without screwing the next guy that has to maintain the code. Arggg

apotheon
apotheon

Real coders use a stream editor. No, wait -- real coders use echo and redirects. Real coders use hand-held magnets to manipulate the hard drive directly. Real coders blow gently on a butterfly to alter its course, setting quantum probability shifts into motion that ultimately cause the code to appear by way of an otherwise unlikely chain of events.

apotheon
apotheon

Do you ever get to consider the possibility of using other types of databases when such are appropriate? This question, of course, may not be relevant to you if everything you do happens to involve making use of large data sets that require both heavy access via arbitrary data relationships and high performance requirements, but if either of those is not strictly necessary a lot of the time there may be other alternatives. Then again, those other alternatives may well require learning new skillsets for which you simply don't have the time. I know I'm far too busy with all the stuff I learn from day to day to spend any of it learning Haskell, so far, even though I'd really like to learn Haskell.

Tony Hopkinson
Tony Hopkinson

and after sql experts, who went that little bit too far... :p The main reason I don't like 'specialists' is all database designs, and their implementations are a set of compromises. 'Experts' tend to lose sight of that and end up with 'perfect' design, which is a git to implement and a total barstard to use. Mix those three up anyway you like, you can't let one facet of the process go off on one. I've seen briliantly implemented rubbish, super fast jaw dropping applications, that fell on their arse, and soon as you breathed near them. Designs so complex the people who were left with them daren't change them. I have worn all three hats on occasion, and gave myself a good talking to a few times, after my making my own job harder. :p Where I agree wholeheartedly is when you get a developer who believes they are proficient at SQL, and get past a select * statement or a table with 200 columns and no primary key and they don't seem to know anything worth mentioning.

Justin James
Justin James

... some folks are not well versed enough in DBs to write code that doesn't jam up the system. Or they make mistakes that a DBA wouldn't (like using a string for a primary key instead of an integer). Etc. Once something has to scale beyond a certain point, I'd rather have an expert (regardless of job title) doing the SQL work, just to ensure proper performance, since the DB is where the large majority of the performance will be lost on a data driven app. J.Ja

apotheon
apotheon

Bad habits can come from any starting point. People who start out working with full-power RDBMSes often develop bad habits as well, first and foremost among them being the tendency to think that a full-power RDBMS is the hammer for every nail -- and everything is a nail. At least people who start with the smaller "desktop databases" don't develop that particular bad habit as often. Of course, if by "desktop databases" what you really mean is "Access", that's another kettle of eels entirely. MS Access is not a bad client for a full-power RDBMS, but the sorry excuse for a "database" management system that is integrated with it is pretty much the wrong tool for every job. In a situation like that, using the integrated DBMS as one's first encounter with database management is almost unavoidably going to give people bad habits. The only way it gets worse is using MS Excel as a DBMS.

merakli
merakli

Thanks for the point of view.

Tony Hopkinson
Tony Hopkinson

becaase of the abysmal lacdk of real coders.... I'm really careful now before I blame the previous unfortunate who worked on the code, I'm now well aware of why I'm not allowed to fix it. :(

bobconway
bobconway

We employ the "blow gently on a butterfly" method, only we use it with a time machine to alter the immediate past so that threads like this one simply evaporate -- i.e. fall through quantum cracks and end-up in somebody else's nightmare. We're a small shop, so our DBA and developers all write quite a lot of SQL. The division of labour exists mainly in the boss' mind, not ours.

TommCatt
TommCatt

You'll notice I never use the word "expert." I want "good" developers, DBAs, SQEs, and architects. Good people, by definition, know about the Golden Triangle of usability, maintainability and performance. They know that "perfect" is only a direction, "good enough" is a reachable goal and "not good enough" is always breathing down our necks. Experts tend to thrive in academic environments but wreak havoc in real world situations. We need them -- they advance the state of the art. But they can be dangerous if allowed to run free.

mattohare
mattohare

All of this division for better skills gets a bit overboard. Where someone is writing a data-based application, they need more than a basic understanding of database technology. Those that don't would be juniors for such a project. Someone else touched on one of the results, that being an elegant database that is not fit for purpose. Even worse, we end up with a web development platform that's great for an object-oriented delivery of pages and absolutely awful way of handling data. It makes the jewel a but tarnished if you ask me.

Tony Hopkinson
Tony Hopkinson

There are so many people developing database apps with so little SQL knowledge, the large majority of the performnance will be lost sucking it all into a client method, because they didn't know about Convert, or Cast or DatePart, or subqueries or even Declare... No point in tuning the database, in those cases.

Tony Hopkinson
Tony Hopkinson

a because it's already there approach, though I agree, it can get silly. Access, paradox, dbase doesn't really matter. Because they are desktops, it's more efficient to select * from Table and then filter or locate client side. That's habit that is an absolute disaster with a server backend. It's using the wrong approach thru ignorance that does me in. In my opinion if your desktop backend is so slow you need use technique one, you are p1ssing into the wind anyway. Besides you end up with sh!t awful code as well.

apotheon
apotheon

Thanks for the point of view. It has been my pleasure to contribute something meaningful to the discussion, really. I aspire to eventually be one of the people working on really interesting stuff in poorly defined fields that defy the expectations of the common daycoder, but for the moment I feel like I'm barely qualified for the relatively well-defined niches in which I develop software.

Duke E Love
Duke E Love

I would imagine that most developers would not even fit into your definition of developer.

Tony Hopkinson
Tony Hopkinson

A developer, is the guy (or gurl) Who makes the bestest scape goat ever. :(

CodeCurmudgeon
CodeCurmudgeon

A software engineer who didn't major in engineering.

Realvdude
Realvdude

A developer is someone that designs a solution and also implements it. Compare to an engineer who can layout a solution and have others make it work. Sorry in advance to any actual engineers that are hands on through the whole implementation phase.

apotheon
apotheon

It's just like a jackaloper, but with cloven hooves instead of rabbit's feet and a barbed tail instead of a cottontail.

mattohare
mattohare

Well, that's the first thing I could think of, having just spent my commute scrubbing data relating to English local authorities.

Shellbot
Shellbot

Someone who's not really a developer..but likes to think they can do if needed :D

Editor's Picks