SQL and beyond

SQL is a common language implemented by most databases. While it’s a nice language it does lack some features available in some databases (which often differ from database to database). Some argue, that you shouldn’t go beyond the contrains and limitations in SQL because that removes your ability to switch to a different database. That’s just wrong (in my opinion).

SQL usually allows you to do all the searches and CRUD operations you need, and by not going beyond the limitations posed by SQL you can switch your application to another database with an absolute minimum number of changes in your application – especially if you utilize the Perl DBI or the Pear DB (in PHP).

By not going beyond SQL you don’t need to worry if your database support the functions you need not to worry if the functions you use exist in the database you use.

While the above may be true, there really much more pushing towards using the extensions to the SQL language which exist in the database you’re currently using.

  • It may make your code faster – by using database native formatting.
  • It may make your database queries faster – as you use functions to retrieve or format the data “in place”.
  • It may make your queries easyer to ready- as they sometimes allows you to express what you want more clearly in the query.

Utilize the tools at your disposal – if the database has extensions, use them if it makes your code faster or more readable. It may be a nice option to be able to “just switch” the database to something else, but how often does that happen and if it should happen some day changing a few lines of code with queries would probably be a small step in the migration plan.

*) CRUD: CReate, Update, Delete