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 ...

Get Learning SQL on SQL Server 2005 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.