I am learning SQL Server 2012 along with C#. In the past, I took database class in college, and have good grasp on SQL language and can write queries. However, looking at SQL Server documentation (or Oracle), it appears that DB engine (along with all the technologies that comes with it) are highly complex and are requiring significant time and effort to learn.

Besides writing SQL queries, how deeply should programmer know about internals of database engine ?

  • Note that SQL is not the end of databases. DDL is also important. There's also a lot more to using a database than running queries, even if you count "START TRANSACTION..." as a query. – Móż Mar 16 '14 at 1:45
  • @Ӎσᶎ What is DDL ? – newprint Mar 16 '14 at 4:20
  • 2
    google.com/?q=database+ddl – Móż Mar 16 '14 at 6:08
  • 1
    I like how this question is now flagged as "primarily opinion-based". Had I the time or inclination I would write a proof against the fact of ORM abstraction leakage, a demonstration of impossible expressions in SQL, and a demonstration of query resolution inequivalence against directly equivalent SQL expressions in two databases. The point is, this sort of thing does affect programmers and their programs and is only manageable through an understanding of what happens in a database and how ORM frameworks work. Didn't even touch non-relational. Guess that's all just "my opinion". – zxq9 Mar 17 '14 at 23:56

Without a sound grasp of database internals, you are bound to misuse it.

Writing SQL which runs, and does its job, but without creating the proper index in the database may work in development, but works very poorly in production, effectively killing your database, and causing bottle-necks.

Identifying a bottle-neck in itself needs a knowledgeable person, since you might (falsely) say - well I've reached the capacity of my DB server, I just need to scale it up... which translates to money loss, and is a very short-term solution.

Performance is not the only problem which may arise - security is a main issue which might not be apparent to the naive developer - O/R mapping and other frameworks made SQL-injection and other database attacks less prevalent, but being unaware of their existence may open up your application to very nasty attacks.

Large-scale DB-oriented projects often have a full-time DBA, whose job is to work with the system architect on the database schema, guiding developers and going over all of their queries, fixing them or adding indexes where needed.

In smaller projects, a team might depend on its own developer's competence for this. This means that you should be able to understand the major guidelines and caveats for using and misusing your database.

If you work in a team, you might want to depend on a team member who is more knowledgeable in the database arena than you, and let him review your SQL queries, and help you design them.

Developers today are expected to be intimately familiar with many technologies, from SQL, noSQL, OS, Web Engines, Mobile, etc. and it is relatively easy to achieve a working prototype in any of these to give a developer the illusion of proficiency, until the same code is faced with production environment, where the true complexity of technology is revealed... you better be prepared!


As deeply as you have time for. There is a lot of fluff these days about being "database agnostic", but unless you like the idea of lowest-common-denominator features defining your best efforts you need to know how your database works. You'll get better results on non-trivial projects if you decide to make your schema and database application agnostic instead of going the other direction.

The last sentence of the previous paragraph flies in the face of just about all Web-2.0 common knowledge. It is worth deciding where your project falls on the "Better is Better" VS "Worse is Better" continuum.

But this does not mean that you should just pick one database to learn about, and what I really mean is that you shouldn't just learn one data paradigm. While fully normalized relational schemas are your best bet for thoroughly understanding your data, they are not workable for many practical applications (but they make an excellent permanent data store from which you can build high-speed warehouses tailored to specific queries later on, if you have the resources). A deliberately denormalized relational schema is usually your backbone, but you often also need a graphing database and -- just as important as your normalized relational schema -- you will probably need to develop a serialization schema (think along the lines of ASN.1 or YAML, not XML, btw -- data, not documents). If you do geographic or spatial data then a GIS-specialized data store can quickly become a must as well. Document databases can also become necessary if you realize one day that what you are doing (or, more realistically, some heavily-loaded aspect of what you are doing) is actually document storage and not record storage.

Having said all that, you will need to understand enough about each to write an intra-db integrity layer (which can be asynchronous, so don't panic) lest they not understand one another. This requires knowing how DBs work.

This sounds like a lot to know, but only because it is. Data is just that important. Sure, you can be like the Web 2.0 guys and kludge your way along... until you hit on a really novel idea and suddenly realize that you can't practically implement your cool idea. Data drives use, not the other way around, and these days its getting shorter and shorter shrift. Don't be the next person who saved a few weeks doing some genuine study of data modeling (in all its forms) only to spend a lifetime of reinventing a data management system that lives only within your one-off project.

It took me a long time to come to this opinion, and my evolution was slow. I didn't need all this knowledge at once; understanding data in a more complete way has come as a side effect of interacting with more interesting aspects of problems that emerged over time as I worked. (In particular, in the process of writing three very large business simulation systems by myself.)

EDIT: It is worth mentioning that the reason I gave special mention to the relational model is that it is possible to simulate any other data model with relational data, but not necessarily possible to simulate every other data model from all of the others. Normalized relational is the most general and therefore the place to start learning how databases and data models work (and these are related ideas with good databases like Postgres, DB2 and Oracle). But pure normalized relational can sometimes be like trying to build a skyscraper with a Swiss Army knife if adhered to religiously. That is why I say you should understand your data in a fully normalized relational way though you may not actually implement it in such a way (or even in a database at all).

In practical terms, if you learn relational data theory thoroughly first you will quickly grok the strengths and compromises inherent in non-relational systems (to include flavor-of-the-week systems) such as CouchDB, AllegroGraph, ObjectDB, etc. and exactly where ORM abstractions fail (IOW, you'll know exactly why 1 class != 1 table && 1 object != 1 row (not even remotely close)) and learn many new things about functional and imperative programming as well.

  • 1
    It may be worthwhile to note that normalization is a big topic unto itself, with most of a dozen different models. (And all of which are a tradeoff between maintenance & storage size vs. performance.) – DougM Mar 15 '14 at 12:14
  • @DougM Yes. A huge topic with literally an infinite number of models, not just a dozen. Any canonical list of model styles is as necessarily incomplete as it is inaccurate. That said, graphing, geo/spatial, hierarchical, etc. paradigms are all topics unto themselves at least as deep as relational data theory. Each form of data storage has consumed multiple academic careers and been the subject of entire subdivisions of libraries. Data paradigms are to programming paradigms as databases are to runtimes as query languages are to programming languages; and genuinely interesting overlaps abound. – zxq9 Mar 15 '14 at 12:29
  • ack, typo. read that "most of" as "more than." (and a normalization model is not a data model...) – DougM Mar 15 '14 at 14:59
  • @zxq9 Did you mean "1 class != 1 table" at the end there? – Móż Mar 16 '14 at 1:47
  • @Ӎσᶎ Bah! Yes, you're right. Corrected to read class != table && object != row. Thanks for catching that. My inner monologue's OOP vocabulary has been all jiggled up for several years. Come to think of it, "object != row" makes it painfully obvious just how insanely leaky most ORM abstractions really are. – zxq9 Mar 16 '14 at 12:07

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