Sunday, April 29, 2007

Hints And Pitfalls In Database Development (Part 3): Database Programming Requires More Than SQL Knowledge

First of all, "knowing SQL" is a rather broad term. SQL basics can be taught within a day, but gaining real in-depth SQL know-how, including all kind of database-specific extensions, might take years of practice.

Then there are always plenty of ways of how to build a solution in SQL, and only a small subset of those are really good ones. It is important to be aware of the implications of certain SQL constructs - which kind of actions the database engine has to undertake in order to fulfill a given task.

Let me provide a little checklist - those are things every database programmer should know about in my opinion:

  • ANSI SQL and vendor-specific additions (syntax, functions, procedural extensions, etc). What can be done in SQL, and when should it be done like that.
  • Database basics (ACID, transactions, locking, indexing, stored procedures, triggers, and so on).
  • Database design (normalization plus a dose of pragmatism, referential integrity, indices, table constraints, stuff like that).
  • Internal functioning (for instance B-trees, transaction logs, temp databases, caching, statistics, execution plans, prepared statements, file structure and requirements for physical media, clustering, mirroring, and so on).
  • How do certain tasks impact I/O, memory and CPU usage.
  • Query optimizer: what it can do, and what it can't do.
  • Error handling, security (for example how to avoid SQL injection, ...).
  • Database tools: profiling, index tuning, maintenance plans (e.g. backup and reindexing), server monitoring.
  • Interpretation of execution plans.

Previous Posts: