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 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 ?

share|improve this question

closed as primarily opinion-based by gnat, MichaelT, Jim G., World Engineer Mar 17 at 14:34

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.

    
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. –  Ӎσᶎ Mar 16 at 1:45
    
@Ӎσᶎ What is DDL ? –  newprint Mar 16 at 4:20
1  
google.com/?q=database+ddl –  Ӎσᶎ Mar 16 at 6:08
    
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 at 23:56
add comment

2 Answers 2

up vote 16 down vote accepted

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 you 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 lost, and 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!

share|improve this answer
add comment

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

share|improve this answer
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 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 at 12:29
    
ack, typo. read that "most of" as "more than." (and a normalization model is not a data model...) –  DougM Mar 15 at 14:59
    
@zxq9 Did you mean "1 class != 1 table" at the end there? –  Ӎσᶎ Mar 16 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 at 12:07
show 4 more comments

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