Hi Friends,

There are n number function are available for SQL server development.

If we try to differentiate between types of function we will get following category

1. Build in Function
2. User Defined Function

Some function are known as build in function because we already know these are directly available in SQL server.

And some function we used to defined for our simplification and coding through coding or in stored procedures are know as User Defined function

I thought of having learning both of them in all way and as well as posting them on same in daily basis.

So here we go with our SQL Server function Series In which we are going to cover each and every aspect of function in all upcoming post

In this post I am going to only cover various Types of Build in Function and User Defined Function.

Main Types Build in Function of Functions

1. Rowset Functions
2. Aggregate Functions
3. Ranking Functions
4. Scalar Functions

Lets dig into each at basic level understanding for each Type

1. Rowset Functions

• Rowset functions return an object that can be used in place of a table reference in a Transact-SQL statement.
• These functions allow you to query a remote data source that might be setup as a linked server on the current instance of SQL Server
• All rowset functions are nondeterministic.
• This means these functions do not always return the same results every time they are called, even with the same set of input values.
• Rowset functions return an object that can be used as if it were a table or a view.

2.Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

3.Ranking Functions

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Useful Ranking functions:

• RANK
• NTILE
• ROW_NUMBER
• NSE_RANK

4.Scalar Functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

Main Types User Defined  Function of Functions

1. Table Valued Function
2. Scalar Valued Function
3. Aggregate Function

Lets dig into each at basic level understanding for each Type

1. Table Valued Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.

1. Scalar Valued Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

1. Aggregate Function

User defined aggregate functions return a single value, calculated from values in a column.

We will have detailed level of description on every function !!

Hope this helps !!

If you like my posting and explanation do Subscribe my blog !!