Linux Today: Linux News On Internet Time.

O'Reilly Network: AboutSQL: Using Functions

Jan 13, 2001, 21:05 (1 Talkback[s])
(Other stories by John Paul Ashenfelter)

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

Complete Story

Related Stories: