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 or COMPUTE BY clause
• A HAVING clause

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.