SQL Functions
SQL has a number of functions to assist you in your database programming.
Functions are a self contained script/program built for a specific purpose. Generally, the value returned by a function will depend on the context in which it is being used. Often, a SQL function will be used within a query and this is what provides it with it's context.
Transact-SQL provides the following types of functions.
- Rowset Functions
These return an object that can be used in place of a table reference in a SQL statement.
- Aggregate Functions
Perform a calculation on a set of values and return a single value. Aggregate functions can be used in the following:
- The select list of a
SELECT
statement - A
COMPUTE
orCOMPUTE BY
clause - A
HAVING
clause
- The select list of a
- Ranking Functions
Returns a ranking value for each row in a partition.
- Scalar Functions
These return a single value from a single value. Scalar functions are categorized as follows:
- Configuration Functions
- Cursor Functions
- Date and Time Functions
- Mathematical Functions
- Metadata Functions
- Security Functions
- String Functions
- System Functions
- System Statistical Functions
- Text and Image Functions
Note that different database vendors have their own built-in functions for their products. Also, most products enable programmers to program their own user defined Functions. For information on proprietary functions, you should consult the vendor's documentation.
In the next lesson we'll look at one of the more commonly used SQL functions — the COUNT
function.