When SQL Functions make sense

Did you know that there are SQL built-in functions, just as we have CF built-in functions? Indeed, have you ever wondered when you should use one or the other?

In usin SQL functions, you're saying you wants the SELECT statement that's passed to the DBMS to have the function passed along with it (not the value of the function, but the function itself) so that the function is executed by the DBMS before the results are returned.

Sometimes, the intention is to simply convert the output being returned in some way, as in to lower case is. In that case, you could do something like this:

<cfquery ...>
Select column1 from MyTable
</cfquery>

<cfoutput query="">
#lcase(column1)#
</cfoutput>

But an argument can be made that asking CF to do this post-processing of the output is something you should avoid, if you can ask the DBMS to do it for you. If you could use the DBMS's built-in function you could instead do as he did in the first example above and say Select ucase(column1) or Select upper(column1), depending on the DBMS. That would take load off of CF and put it on the DBMS.

Now, it may seem that the approach is splitting hairs. The output is the same either way, so why worry about SQL functions at all. But what if instead you wanted to execute a function that altered the way that the search results were found?

For instance, consider a column called startdate. What if what you wanted to find only those records whose startdate was this year? While you could do some date range comparisons on the select, or (worse) just select all the data and search though it using CF functions, you could very easily (in some databases) use where year(startdate) = year(now()).

While those may look like CF functions, they're all SQL functions. While you could replace the right side of the equal sign with the CF functions (wrap them in pound signs), you couldn't do the same with the left side. That would create a value that's passed to the SQL, when instead you want to operate on the data in the database.

So SQL functions do indeed have their place.

In the previous entry, I provided some reference to finding out more about SQL functions.