What is Use scalar functions

Scalar functions return a single value and usually work on a single row of data. The number of input values they take can be zero (for example, GETDATE), one (for example, UPPER), or multiple (for example, ROUND). Because scalar functions always return a single value, they can be used anywhere a single value (the result) is needed. They are most commonly used in SELECT clauses and WHERE clause predicates. They can also be used in the SET clause of an UPDATE statement.

Built-in scalar functions can be organized into many categories, such as string, conversion, logical, mathematical, and others. This module will look at a few common scalar functions.

Some considerations when using scalar functions include:

• Determinism: If the function returns the same value for the same input and database state each time it is called, we say it is deterministic. For example, ROUND(1.1, 0) always returns the value 1.0. Many built-in functions are nondeterministic. For example, GETDATE() returns the current date and time. Results from nondeterministic functions cannot be indexed, which affects the query processor’s ability to come up with a good plan for executing the query.
• Collation: When using functions that manipulate character data, which collation will be used? Some functions use the collation (sort order) of the input value; others use the collation of the database if no input collation is supplied.