What is the type Functions in SQL?Explain user defined function with syntax

Types of Functions in SQL Server

There are two types of functions in SQL Server:

1. System Functions: All the built-in functions supported by the Server called System functions in SQL Server. We don’t have to bother about the logic inside them because they cannot be modified. For example, Mathematical Functions, Ranking Functions, String Functions, etc.

2.  User Defined Functions: The User Defined Functions in SQL Server are like functions in any other programming language that accepts the parameters, performing complex calculations, and returning the result value. 

SQL Server allows us to create our functions called as user defined functions in SQL Server. For example, if we want to perform some complex calculations, then we can place them in a separate function, and store it in the database. Whenever we  need the calculation, we can call it. There are two types of SQL user defined functions:

  

•           Scalar Function: It is a function that returns a single value. Generally, we have to define the function body between BEGIN … END block, but for inline scalar function, you can omit them. We can use any SQL data typeas the return type except text, image, ntext, cursor, and timestamp.
• Table Valued Functions: It is a user defined function in SQL Server that returns a table.
  • • Inline Table valued Functions: This function returns a table data type based on a single SELECT Statement
  • Multi Statement Table valued Functions: This function also returns the tabular result set. But, unlike the inline table valued function, we can use multiple select statements inside the function body.

Advantages of User Defined Functions in SQL Server

1. User defined functions in SQL Server prevent us from writing the same logic multiple times.
2. Within the Database, you can create the function once, and call it n number of times.
3. SQL user defined functions reduce the compilation time of query by catching the execution plan and reusing them.
4. This user defined functions can help us to separate the complex calculations from the regular query so that we can understand and debug the query quicker and better.
5. It reduces the network traffic because of its cache plan
6. SQL Server Functions also used in WHERE Clause as well. By this, we can limit the number of rows sent to the client.

Syntax of User Defined Functions in SQL Server

The syntax the SQL server User defined functions or UDF is

CREATE FUNCTION Function_Name(@Parameter_Name Data_type, 
                                 .... @Parameter_Name Data_type
                             )
RETURNS Data_Type
AS
   BEGIN
      -- Function Body
      
      RETURN Data 
   END

• Return_Type:
  1. Data Type: Please specify the data type of return value. For example, VARCHAR2, INT, FLOAT, etc.
  2. Data: Please specify the return value, and it should match the Data Type. It can be a single value or Table
• Function_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.
• @Parameter_Name: Every function accepts zero or more parameters; it completely depends upon the user requirements. While declaring the parameters don’t forget the appropriate data type. For example (@name VARCHAR2(50), @number INT)
• Function Body: Any query, or any complex mathematical calculations you want to implement in this particular function.