Function, function in sql

We all are from programming background and we know that what is function and why we need it. But I am going to recall that what function is. Function provide us encapsulate reusable logic. In other words function hides the step and complexity of code and we use that function where we want.

Why we use function
There are benefits of using function. Some of them are following.

We can use user defined function in select as well as where class.

We can use user defined function used as rowset.

We can use user defined function as parameterise views.

Type of function

SQL provide two type of function.

System defined function

User defined function

System defined function
System defined functions are provided by SQL itself. You can find the detail of these functions on following. For more details click here.

User defined function
User defined function are two type. First is Scalar and second is Table value.

Scalar Function
These functions return only single value. These functions any data type like it could be return int , varchar or datetime. Let’s understand by example. Let’s create a table and name it myTable.

Let’s create a scalar function. This function will do some calculation. This function perform some calculation like it check the date on the behalf of id and if that date contain first month of the year the it returns 0 else it perform this formula.