Learning SQL on SQL Server 2005

Chapter 5. Functions

Functions are preprogrammed mini-programs that perform a certain task. As with mathematics, functions transform values into another result. SQL Server 2005 has a wide range of built-in functions to carry out various tasks. In this chapter, we introduce several of SQL Server 2005’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 ...