SQL Server Functions

Transact-SQL provides many functions that return information. Functions take input parameters and return values that can be used in expressions. The Transact-SQL programming language provides three types of functions, aggregate, scalar, and rowset.

Aggregate Functions

Aggregate functions operate on a collection of values but return a single, summarizing value.

Example 1

This example determines the average of the UnitPrice column for all products in the Products table.

SELECT AVG(UnitPrice) FROM Products

Result

		Products
		28.8663
		(1 row(s) affected)

Scalar Functions Scalar functions operate on a single value and then return a single value. You can use these functions wherever an expression is valid. You can group scalar functions into the categories in the following table. Function category Description Configuration Returns information about the current configuration Cursor Returns information about cursors Date and Time Performs an operation on a date and time input value and returns a string, numeric, or date and time value Mathematical Performs a calculation based on input values provided as parameters to the function and then returns a numeric value Metadata Returns information about the database and database objects Security Returns information about users and roles String Performs an operation on a string (char or varchar) input value and returns a string or numeric value System Performs operations and returns information about values, objects, and settings in SQL Server System Statistical Returns statistical information about the system Text and Image Performs an operation on a text or image input value or column and returns information about the value

Example 2 This metadata function example returns the name of the database currently in use. SELECT DB_NAME() AS 'database' Result Database Northwind (1 row(s) affected) Rowset Functions Rowset functions can be used like table references in a Transact-SQL statement. Example 3 This example performs a distributed query to retrieve information from the EMP table. SELECT * FROM OPENQUERY(OracleSvr, 'SELECT ENAME, EMPNO FROM SCOTT.EMP')