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:

• Missing Indices
• Let The Database Enforce Data Constraints

Follow-Ups:

• Do Not String-Concatenate SQL Parameter Values
• The Importance Of Choosing The Right Clustered Index