Different Type Of SQL Functions And Their Uses

Introduction

Structured Query Language or SQL is a programming language utilized for managing relational databases. The most helpful feature of SQL is to utilize functions to perform multiple operations on the data in a database. Imagine a scenario when you are writing a complex query where you need to add up a series of values. How would you do it?

The approach is to use functions. SQL Server has a lot of built-in functions already defined in the system. You can use them if they serve your purpose. For example, in the case of the above scenario, you can use the SUM() function and frame your query like below: 

Select sum(Val) from .

Even if the already provided system-defined functions do not serve your purpose, SQL Server allows developers to write their functions. Let's explore in detail the different types of SQL Functions.

What is SQL Function?

SQL Functions are programs either developed by the user or already provided by the SQL Server system, which can be used to perform specific repetitive tasks.

Different types of SQL Functions with Examples

System-Defined Function	User-Defined Function
SQL Server defines this function	Any SQL functions developed by the user
Three types of functions	Any functions developed by a user

System-Defined Functions

These are functions that are already defined in the system

Different types of System Defined Functions

Aggregate Functions	String Functions	Date Functions	Advanced Functions
These are system-defined functions that deal with numbers	These are system-defined functions that deal with strings	These are system-defined functions that deal with a date.	These are system-defined functions which perform a certain complex task like logical conditions etc
Sum(), AVG(), MAX(), MIN(), COUNT() etc. are some example	LTRIM(), RTRIM(), LEN(), LEFT(), RIGHT(), LOWER() etc. are some of the example	GETDATE(), DATEADD(), DAY(), MONTH(), YEAR() etc. are some of the example	IIF(), CAST(), CONVERT(), CURRENT_USER(), ISNUMERIC() etc. are some examples.

Aggregate Functions and its example

These functions are used to perform calculations on a set of values and return a single result. We will be using SalesOrderDetail of Adventureworks database for the below examples.

Sum()

Select sum (OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This sums up the OrderQty column value of SalesOrderDetail table.

 The output is

Avg()

select avg(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives the average of the OrderQty column of the SalesOrderDetail table.

Max()

select max(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the maximum value of the OrderQty column of the [SalesOrderDetail] table.

Min()

select min(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the minimum value of the OrderQty column of the [SalesOrderDetail] table.

Count()

Select count (*) from [Person]. [Person]

Returns a total number of records in the Person table

String Function with Example

There are multiple string manipulation functions provided by SQL.

LTRIM()

Removes space from the left side of the string

select ltrim(' tes')

RTRIM()

Removes space from the left side of the string

select Rtrim('tes  ')

Len()

Gives the length of the string.

select Len('Test')

LEFT()

select Left('Sanchayan',3)

This SQL Statement extracts three characters from the left side of the string.

RIGHT()

select Right('Sanchayan',3)

The above SQL Statement extracts three characters from the right side of the string

Date and Time function with Examples

A number of functions for working with date and time values are provided by SQL. Some of the date and time functions in SQL, with examples, include.

• NOW() - Returns the current date and time
• CURRENT_DATE() - Returns the current date
• CURRENT_TIME() - Returns the current time
• YEAR() - Returns the year of a date
• MONTH() - Returns the month of a date
• DAY() - Returns the day of a date

GETDATE()

Gives out the current date

select GETDATE()

DATEADD()

SELECT DATEADD (month, 1, '20060830');

Add a month with the date value 20060830

DAY()

select day('12/18/2019')

The SQL Statement gives the current day value of the date passed as parameter.

MONTH()

select MONTH('12/18/2019')

The SQL Statement gives the current month value of the date passed as a parameter.

YEAR()

select YEAR('12/18/2019')

The SQL Statement gives the current year value of the date passed as a parameter.

Advance Function with example

IIF()

Can be used for if else condition in a single select statement.

The following query gives out MALE if the gender is male and FEMALE otherwise.

Select JobTitle,iif(Gender='M','MALE','FEMALE') [GENDER] from [HumanResources].[Employee]

CAST()

SELECT CAST(25.65 AS int)

This converts the value 25.65 into an integer.

The output is

CONVERT()

Converts a string into a different data type here integer.

SELECT CONVERT(int, 25.65);

The output is as below

CURRENT_USER

This advance function gives out the current user of the system

select CURRENT_USER

ISNUMERIC()

This function checks whether the parameter passed in it is numeric or not.

select ISNUMERIC(5)

This gives out 1 if true and 0 if false.

User-defined Functions

User-defined functions are functions that the user develops.

Different types of User-Defined Functions

Scalar Function	Table Valued Functions
User-defined function that returns a single value	User-defined functions that returns more than one value

Scalar Function In SQL with Example

The following code is a simple function which accepts two integer values and returns the sum of the two integers.

CREATE FUNCTION Func_Add_

(

@val1   int,

@val2   int

)

RETURNS int

BEGIN

Return @val1 + @val2;

END

 

To execute the function, we need to run the following command.

select [dbo].[Func_Add_] (2,3)

The output is

Different types of table-valued function

Inline table-valued function	Multi statement table-valued function
Returns a table object as output	Returns a table variable as output
Includes only one select statement	Include multiple statements
The processing time is faster	The processing time is slower

Inline table-valued function example

We will be using the SalesOrderHeader table of AdventureWorks database. This particular function accepts a date as a parameter and gives out all the sales order details on that particular date. 

CREATE FUNCTION func_inlinetablelevel (

@order_date date

)

RETURNS TABLE

AS

RETURN

select * from [Sales].[SalesOrderHeader] where

OrderDate = @order_date;

To run the function we need to use the following statement

select * from func_inlinetablelevel('2011-05-31')

The output is

Multi statement table valued function

This particular query accepts a sales order id and returns the total quantity sold against the order.

create FUNCTION tablemultivaluedfunctioneg  (@Parameters int)

RETURNS @FunctionResultTableVariable TABLE (N int)

AS

BEGIN

INSERT INTO @FunctionResultTableVariable

SELECT OrderQty from [Sales].[SalesOrderDetail]

where SalesOrderID=@Parameters ;

RETURN;

END

GO

WE can run the function using the following statement

select * from tablemultivaluedfunctioneg(43659)

The output is

Different between Functions and Procedures

Stored Procedure	Functions
Compiled only once and executed again and again	Compiled every time before execution
It is optional to return a value	Function always returns a value
Cannot be called from a function	Can be called from a stored procedure
Cannot call procedure within a procedure	Can call a function from within a function

Calling a Function Within a Function

Did you know that a function can also be called from within a function. Let's see how to call a function from within a function. This example will demonstrate how we will call the get date function from within the year function.

Here goes the query statement

select year(getdate())

Here is the output

Few more complicated function examples

The first one is to create a Fibonacci number series using functions. We will pass the number of rows as a parameter.

Fibonacci number series as we all know looks like this

0

1

1

2

3

5

8

13

Now let us see how the code looks like

CREATE FUNCTION fn_Fibonacci(@max int)

RETURNS @numbers TABLE(number int)

AS

BEGIN

         Declare @n1 int = 0,@n2 int =1,@i int=0,@temp int

         Insert Into @numbers Values(@n1),(@n2)

         WHILE (@i<=@max-2)

         BEGIN

                     Insert Into @numbers Values(@n2+@n1)

                     set @temp = @n2

                     Set @n2 = @n2 + @n1

                     Set @n1 = @temp

                     Set @i += 1

         END

         RETURN

END

To execute the function we need to write

select * from [dbo].[fn_Fibonacci] (15)

Conditional Functions with Examples

This function performs different actions based on certain conditions. Some of the conditional functions in SQL with examples include −

• CASE - This function evaluates a list of conditions and returns a result for the first condition that is met
• IF - This function returns a specified value if the condition is met, otherwise returns another specified value
• COALESCE - This function returns the first non-null expression among multiple expressions.

An example of using the CASE function to assign a label to each order based on the total cost −

SELECT order_id, total_cost,

   CASE 

      WHEN total_cost > 100 THEN 'expensive'

      WHEN total_cost > 50 THEN 'moderately priced'

      ELSE 'inexpensive'

   END as "price range"

FROM orders;

Here's an example of using the IF function to check the availability of stock of a product

SELECT product_name,

   IF(stock_quantity > 0, 'In Stock', 'Out of Stock') as

   "Availability"

FROM products;

This is an example of how the COALESCE function displays the primary phone number and the secondary phone number of a customer when utilized −

SELECT customer_name, 

COALESCE(primary_phone, 

secondary_phone) as "Phone Number" 

FROM customers;

Logical Functions with Examples

Logical functions return a Boolean value, which can be true or false. Some logical functions in sql with examples include −

• AND - This function returns true if both the conditions are true
• OR - This function returns true if at least one of the conditions is true
• NOT - This function negates a boolean value

Here's an example of the AND function to find all customers who live in a specific city and have an account balance greater than a certain amount when utilized −

SELECT customer_name, city, account_balance

FROM customers

WHERE city = 'New York' AND account_balance > 1000;

Conversion Functions with Examples

These functions can be utilized to convert data from one type to another. Some examples of conversion functions in SQL are −

• CAST() - This function converts a value from one data type to another
• CONVERT() - This function converts a value from one data type to another (This function is specific for SQL Server)
• TO_DATE() - This function converts a string to a date value
• TO_TIME() - This function converts a string to a time value
• TO_TIMESTAMP() - This function converts a string to a timestamp value

Here's a sql function example of utilizing the CAST() to convert a float value to an int −

SELECT CAST(price AS INT) as "Integer Price"

FROM products;

Here is an example of using the TO_DATE() to convert a string to a date value −

SELECT TO_DATE(order_date, 'yyyy-mm-dd') as "Formatted Order Date"

FROM orders;

Window Functions with Examples

These functions can be utilized to perform calculations across a set of rows related to the current row. Some examples of window functions in SQL are −

• RANK() - This function devotes a unique rank to each row within a result set, based on the values acquired in one or more columns
• DENSE_RANK() - This function assigns a particular rank to each row within a result set based on the values in one or more columns but makes sure not to leave any gaps in the ranking sequence when there are ties
• ROW_NUMBER() - Assigns a unique number to each row within a result set based on the order specified in the ORDER BY clause of the function

Here's a sql function example of utilizing the RANK() to find the rank of each customer based on their account balance −

SELECT customer_name, account_balance, RANK() OVER (ORDER BY account_balance DESC) as "Rank"

FROM customers;

Here's a sql function example of utilizing the ROW_NUMBER() to find the row number of each customer in the table −

SELECT customer_name, ROW_NUMBER() OVER (ORDER BY customer_id) as "Row Number"

FROM customers;

These are just some of the few examples of the many functions like scalar functions in SQL and many others. SQL functions in dbms provide for working with and manipulating data in a relational database. Each category of functions has been predefined with its unique purpose and understanding of when and how to use them. This can help to work with SQL and relational databases more efficiently and effectively. All this information will be a game-changer in the long run when you pursue a career path in SQL.

Summary

We have curated some of the essential aspects of SQL Functions. This is not a comprehensive study, but it will give the reader an idea about the different types of SQL Functions and their uses. 

Become a complete Microsoft SQL professional with a sql server certification online. Choose from plenty of sql server online training and enroll in the best program. Boost your career with the best online sql server training.

FaceBook Twitter Google+ LinkedIn Pinterest Email