68  SQL scalar functions

SQL scalar functions enables you to perform different operations operations. Scalar functions returns single value even when they have more than one input. Below example of min function has 4 inputs but output is single value of 3.

select min(3,5,6,11);

Every database provides some core functions in string, mathematical, date and time. Most of these functions are scalar but some of them returns table like structures.

Previously in our course, we have covered following types of functions:

For our course, we will see following type of functions:

68.1 SQL deterministic functions

Deterministic function gives to same output whenever input is the same. For example random, date functions are non deterministic since whenever you call them, you get different output.

Date functions like sqlite date, sql server GETDATE and oracle sysdate are non deterministic since they return current date, this is same for 24 hours period, but after that it changes.

Deterministic functions also should not have any side effects. That is they should not change anything when they are called. This condition is mostly about the functions, you write.

If database engine knows that a function is deterministic, function will have some speed optimizations and also could be used in indexes.

68.2 SQL Custom or User-defined functions

Most databases also allows you to write custom functions SQL dialects. These type of functions are also called user-defined functions. Sqlite does not have create function keyword to create functions but it allows c-language extension functions to be used in sql. Both oracle and sql server Create function keywords.

We will not cover this topic any more in the course.