A function is an operation represented by a function name followed by input values enclosed in parentheses. It connects a set of input values and a set of result values. The input values are called arguments. Functions can be grouped into aggregate, scalar, table, and row types.
Table of contents
Aggregate functions
An aggregate function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values. Certain rules apply to all aggregate functions.
Scalar functions
A scalar function can be used wherever an expression can be used. The restrictions on the use of aggregate functions do not apply to scalar functions, because a scalar function is applied to a single set of parameter values rather than to sets of values.
The argument of a scalar function can be a function. However, the restrictions that apply to the use of expressions and aggregate functions also apply when an expression or aggregate function is used within a scalar function.
For example, the argument of a scalar function can be an aggregate function only if an aggregate function is allowed in the context in which the scalar function is used.
Table functions
A table function can be used only in the FROM clause of a statement. Table functions return columns of a table and resemble a table created through a CREATE TABLE statement. Table functions can be qualified with a schema name.
Row functions
A row function can be used only in contexts that are specifically described for the function.
Note: A user-defined function cannot be a Row function (Ref: IBM)
Conclusion
The different types of functions in SQL serve unique purposes and allow for powerful data manipulation and analysis. Aggregate functions operate on multiple values and return a single result, while scalar functions can be used as expressions and operate on individual parameter values. Table functions, on the other hand, are used in the FROM clause and return columns resembling a table. Row functions have specific contexts and limitations.
Understanding and utilizing these types of functions can greatly enhance the efficiency and effectiveness of SQL queries. Whether you need to perform calculations on large datasets, manipulate individual values, retrieve data from complex table structures, or apply functions within specific contexts, SQL functions provide the necessary tools.
By leveraging the appropriate function types, you can optimize your SQL queries and achieve desired results in a more precise and efficient manner. So, ensure you have a good grasp of these different function types to write better SQL queries and effectively harness the power of SQL in your database operations.
Srinimf 
You must be logged in to post a comment.