Understanding SQL Functions- Standard Query Language for Databases

To view the complete tutorial, click here

Understanding SQL Functions

Functions serve a wide range of purposes within SQL. You can use aggregate functions to perform calculations on grouped values within a query. That is one very common use of functions within SQL. However, there are other types of functions that can serve other purposes within SQL, as well. In this lesson, you will learn about the various types of functions within SQL and how they can be used within queries.

A function is simply a computation that is executed by invoking a single-word command that returns a value. While some functions, such as SUM, are universally accepted by all RDBMS, there are also some variations of function names between RDBMS. For example, while almost all RDBMS can provide you with the current date in a function, the name of the function used to accomplish that is SYSDATE within MySQL and Oracle, while it is named GETDATE within SQL Server and DATE within Access. Therefore, you need to check the documentation of your specific RDBMS to know which function names are available to use.

All functions can be classified as being either deterministic or non-deterministic. A deterministic function will always return the same result whenever it is used with the same set of inputs. A non-deterministic function may return a different result when used with the same set of inputs. For example, the SUM function is a deterministic function, as it will always return the same result if given the same input parameters or variables. In contrast, the SYSDATE or GETDATE functions, which are used to retrieve the current date in MySQL or SQL Server, are non-deterministic functions as they will return a different answer when used, even though they use a consistent input.

One reason to know whether a function is deterministic or non-deterministic is because different RDBMS will allow for slightly different use of deterministic and non-deterministic functions. For example, in SQL Server 2012 you cannot create an index on a computed column that references non-deterministic functions. You will need to check the documentation of your specific RDBMS to determine what restrictions may apply to the use of deterministic and non-deterministic functions.

Aggregate functions are considered one of the primary types of functions within SQL. These functions perform a summarizing operation upon a group of values or inputs to return a summary value. In contrast to this, there are also scalar functions. A scalar function is performed upon a single value and returns a single value. For example, the SUM function is used to create a summary value from selected values within a field. However the LOWER function (called LCASE in Access), will simply return a single field value in all lowercase characters.

You can use scalar functions in many places within SQL. While aggregate functions are most often used in conjunction with the GROUP BY clause, unless it is the only value returned by a SELECT statement, scalar functions have more flexibility in where they can be used. You can use scalar functions within the SELECT clause of a SELECT statement to create calculated fields, the INSERT INTO clause of the INSERT statement to insert calculated values, and many other places within SQL. Below is a listing of hyperlinks that show the available scalar functions within MySQL 5.7, SQL Server 2012, and Access 2013.

MySQL 5.7 (Function and Operator Reference):

https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

SQL Server 2012 (Scalar functions grouped by categories):

https://technet.microsoft.com/en-us/library/ms174318.aspx

Access 2013:

https://msdn.microsoft.com/en-us/library/office/ff835353.aspx