Discovering SQL: A Hands-On Guide for Beginners

4

Overcoming the Limitations of SQL

Structured Query Language (SQL) is a set-based language. As such, it is poorly equipped to handle situations in which procedural thinking is required. A SELECT statement, with a little help from the database engine, would almost instantaneously comb through millions of records, perhaps perform JOIN, UNION and ORDER operations, filter the data according to precise search criteria, and more. Yet, as software developers have discovered, there would come a time when you need to pay closer attention to your data, and manipulate it row by row, field by field. This is where SQL functions, and especially procedural extensions, enter the scene.

SQL functions exist to make your life easier, and, to some extent, alleviate the procedural deficiency of set-based SQL. While a query is busy retrieving some data for you, the functions used within that query are validating, converting, calculating, getting system information, and more. Think of them as tools designed to accomplish a single well-defined task (calculating a square root or converting lowercase letters into uppercase, for example) and doing it for each and every row that the query fetches. Just call it by name and pass some arguments (or not), and see your data transformed.

The list of SQL functions available for use within a particular relational database management system (RDBMS) implementation grows with every new release, and some vendors are allowing users to define their own custom functions ...