“SQL, like most computer languages, includes the capability to
perform functions on data. Unlike most computer languages,
however, each database vendor has implemented their own set of
functions. Not only does each vendor choose which functions
they will support, different vendors implement the same
functionality using different syntax! This basically means that
as soon as you open the door to functions, your database code stops
being portable. For traditional database applications, that is
less of an issue, but for web-based applications using tools like
PHP or ColdFusion, it means that the SQL functions you embed in
your web application will break when you move from MySQL to Oracle
or Access to SQL-Server. To make matters worse, SQL functions often
provide significant performance gains for all sorts of database
manipulation, particularly in the case of web applications.”
“So what’s a conscientious developer to do? My advice is to
use functions anywhere you can produce a measurable performance
gain and document thoroughly what database the SQL was intended to
work with. As we’ll talk about in a future column, moving any
function-oriented code to a database stored procedure and
essentially “black-boxing” it is an excellent approach — this lets
the DBA rewrite the stored procedures for optimal performance —
but some databases do not support stored procedures, so this is not
a perfect solution. There are a number of functions that are
available in a wide range of databases, though (even if the syntax
changes), so you can usually get the functionality you need as long
as the functions are not too exotic.”