Chapter 5. Functions

Functions are preprogrammed mini-programs that perform a certain task. As with mathematics, functions transform values into another result. SQL Server has a wide range of built-in functions to carry out various tasks. In this chapter, we introduce several of SQL Server's useful built-in functions, which can be divided into row-level functions, aggregate functions, and other special functions. Row-level functions operate on a row at a time, whereas aggregate functions operate on many rows at once.

In SQL Server, we can group the row-level functions into four types: numeric functions, string functions, conversion functions, and date functions. Numeric functions are used for calculations. An example of a numeric function is the SQUARE function, which would return the square (a row at a time) of every number (row) of a particular column. String functions are used to manipulate strings in a particular column (again, one row at a time). An example of a string function is SUBSTRING, which extracts characters from a string. Conversion functions are used to convert a particular column (a row at a time) from one data type to another. And, date functions (created using the DATETIME data type) operate on a particular data column or attribute, a row at a time. Date functions are also considered fundamental to the operations of a database.

The second category of functions that we will discuss is aggregate functions. Aggregate functions provide a one-number result after calculations based on multiple rows. Examples of aggregate functions are MIN or AVG, which stand for the minimum or average, respectively, and return the minimum or average value respectively, of multiple rows of a particular column.

The third category of functions that we will discuss is a special class of "other" functions. These other functions produce a smaller subset of rows from multiple rows. Example of these other kind of functions would be the DISTINCT function or the TOP function, both of which produce a smaller subset of rows from the complete set.

Note that most of the functions discussed in this chapter are placed in a SELECT statement, and so they are "read-only" or "display-only" functions. Any SELECT statement function will not change the underlying data in the database. To change the underlying data in a database, UPDATE (instead of SELECT) would have to be used (as shown in Chapter 3).

We begin the chapter by discussing aggregate functions. We discuss row-level functions later in the chapter.