Which of the following are built-in functions in Microsoft SQL Server?

Which of the following are built-in functions in Microsoft SQL Server?

• SUM, AVG, MULT, DIV, MIN
• SUM, AVG, MIN, MAX, NAME
• SUM, AVG, MIN, MAX, MULT
• COUNT, SUM, AVG, MAX, MIN

EXPLANATION

COUNT, SUM, AVG, MAX, and MIN are all built-in functions in SQL.

In SQL a built-in function is a piece for programming that takes zero or more inputs and returns a value.  An example of a built-in functions is ABS(), which when given a value calculates the absolute (non-negative) value of the number.
Some
functions, such as ABS() are used to perform calculations, other such as GETDATE() are used to obtain a system value, such as the current data, or others, like LEFT(), are used to manipulate textual data.

Here is a simple query using the absolute value function.

SELECT Name,
       ABS(500 - ReorderPoint) ReorderPointDeviation
FROM   production.Product
WHERE  ABS(500 - ReorderPoint) > 200

In this query we first calculate the difference between 500 and a product’s reorder point.  The ABS function is then used to return the result as a positive number.
There are several things to note regarding functions.

1. The inputs to a function are called parameters.  Not all function have parameters, and some functions have more than one.
2. Parameters are enclosed in parenthesis.
3. We use functions in the SELECT clause as well as WHERE filter condition. A function can be used anywhere in a SELECT statement that you can use an expression.
4. Function are reserved words. I would avoid using them as column or table names.  If you do, then expect to qualify your names with brackets [].

As you learn more about functions you soon find they are vital in being able to calculate and manipulate your query results.  We’ll dig deeper into specific functions and their uses in future posts, but before we do so, let’s talk about the type of data a function can return.