Types of SQL Server Built-In Functions: Scalar, Grouped Aggregate, Window, Rowset

SQL Server offers many number of built-in functions for getting commonly required operations done, minimizing the time and resources need to implement them for handling business logics. These functions can be categorized with various classifications. One way of categorizing them is, understanding their scope of input and return type. Under this categorization, they can be organized into four groups: Scalar, Grouped Aggregate, Window, and Rowset. The organization of functions under this categorization makes some of functions member of more than one group.

Here is a small note on this categorization.

Scalar Functions
These functions accept zero or one or more than one value (or a row), process them as per its intended functionalities, and return a single value. These functions can be further categorized as string, conversion, mathematical, etc.. Some of these functions are deterministic  and some are nondeterministic. (Deterministic functions always return same result any time they are called with same input and same database state. Nondeterministic functions may return different result each time they are called with same input and same database state) Here are some examples for Scalar Functions;

1. -- accepts no arguments
2. SELECT GETDATE()
3.  
4. -- accepts one argument
5. SELECT LEN('Hello World')
6.  
7. -- accepts multiple argument
8. SELECT STUFF('accepts one argument', 9, 3, 'multiple')

Grouped Aggregate Functions
These functions perform their intended operations on a set of rows defined in a GROUP BY clause and return a single value. If GROUP BY is not provided, all rows are considered as one set and operation is performed on all rows. All aggregate functions are deterministic and ignore NULLs except the COUNT(*).

1. -- Aggregate functions without GROUP BY
2. SELECT COUNT(*), MIN(ListPrice), MAX(ListPrice)
3. FROM Production.Product
4.  
5. -- Aggregate functions with GROUP BY
6. SELECT YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
7. FROM Sales.SalesOrderHeader
8. GROUP BY YEAR(OrderDate)

Window Functions
The operation of Window Functions are bit different from other functions. These functions produce a scalar value based on a calculation done on a subset of the main recordset. The subset used for producing the result is called as the Window. A different order can be defined for the window for performing the calculation without affecting the order of input rows or output rows. In addition to that, partitioning the window is also allowed.

Window is specified using OVER clause with its specification. SQL Server offers number of Window function for handling ranking, aggregation and offset comparisons between rows.

Here are few example on Window Functions;

1. -- This uses SUM over Territory subsets (window)
2. SELECT t.Territory, t.OrderYear, t.Total
3.     , SUM(t.Total) OVER (PARTITION BY MONTH(t.Territory)) TotalByTerritory
4. FROM (SELECT TerritoryId Territory, YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
5.     FROM Sales.SalesOrderHeader
6.     GROUP BY TerritoryId, YEAR(OrderDate)) as t
7. ORDER BY 1, 2
8.  
9. -- This uses ROW_NUMBER over all records
10. SELECT SalesOrderID, SubTotal
11.     , ROW_NUMBER() OVER (ORDER BY SubTotal) AS OrderValuePosition
12. FROM Sales.SalesOrderHeader
13. WHERE YEAR(OrderDate) = 2008
14. ORDER BY SalesOrderID

Rowset Functions
Rowset functions accept input parameters and return objects that can be used as tables in TSQL statements. SQL Server offers four Rowset functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML) and they are nondeterministic functions.

Here is an example for it.

1. -- Loading data from a text file
2. -- as single BLOB
3. SELECT * FROM
4. OPENROWSET(BULK N'D:\TestData.txt', SINGLE_BLOB) AS Document