SQL Server functions

SQL Server Identifier 

What is built-in function? Explain its type i.e. Rowset, Aggregate and scalar.

Answer
A built in function of sql is used for performing calculations. These are standard functions provided by sql.

Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX()

Scalar functions: these functions perform calculation on an input value and return a single value. Example: ROUND(), MID(), LCASE(), UCASE()

What is user-defined function? Explain its types i.e. scalar and Inline table value user-defined function.

Answer
User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures

Types:
Scalar user defined: returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters.

Inline table value user defined: it returns a table data type. These functions can pass parameters to the sql’s SELECT command. They are similar to views except, they can accept parameters.

What are the benefits of User-Defined Functions?

Answer
a. Can be used in a number of places without restrictions as compared to stored procedures.

b. Code can be made less complex and easier to write.

c. Parameters can be passed to the function.

d. They can be used to create joins and also be sued in a select, where or case statement.

e. Simpler to invoke.