Types

• Built-in functions are grouped based on the type of action they perform. Functions that return an object are called Rowset Functions. Those that return a single, summarized value, such as an average, are called Aggregate functions. Ranking functions return a ranking value for each row in a partition. Scalar functions return a single value based on a single value in the database.
  
  

Rowset Functions

• Some common rowset functions are OPENQUERY and OPENROWSET. These functions allow you to work with other datasources. Rowset functions are nondeterministic, meaning that the value returned by the function can vary every time the function is called, even if the input values are the same.
  
  

Aggregate Functions

• Aggregate functions cover most statistical calculations. This could include calculating minimum (MIN), maximum (MAX) and average (AVG) values of rows of data. These functions are deterministic, meaning that given the same input values each time, they will return the same data. Each aggregate function returns only one value.
  
  

Ranking Functions

• Ranking functions are a nondeterministic function used for ranking data in partitions. There are four ranking functions: RANK, NTILE, DENSE_RANK and ROW_NUMBER. A ranking function is a method of ordering data. Note that it is possible for multiple rows to receive the same rank.
  
  

Scalar Functions

• Scalar functions encompass many other types of functions, including math, string, text and image functions. In addition, there are functions in this category to manipulate database settings, such as ODBC commands, metadata on columns and tables, statistic information and security settings. This category also encompasses cursors, triggers and cryptography functions.