Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

Different Type Of SQL Functions And Their Uses

Imagine a scenario such as this. You are writing a complex query where you need to add up a series of values. How would you do it?

One way of doing it is as below.

Select val1 + val2 + val3 from <Table Name>.

But this particular approach has a drawback. Today you might have five values and you have framed your query based on that. What will happen when tomorrow the number of value increases? Would you again get inside the query and change? The above query may look simple. But what if you have a complex query where you have written the above type of statement more than once.

Another 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 case of the above scenario you can use the SUM() function and frame your query like below :

Select sum(Val) from <Table Name>.

Even if the already provided system-defined functions does not serve your purpose, SQL Server also allows developers to write their functions.

Over the next few paragraphs, we will learn about 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 certain repetitive tasks.

Different type of SQL Functions

System Defined Function User-Defined Function
This function is defined by SQL Server. 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.

System defined functions

Different types of System Defined Functions

Aggregate Functions String Functions Date Functions Advanced Functions
These are system defined functions which deal with numbers These are system defined functions which deals with strings These are system defined functions which deals with date. These are system defined functions which performs 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 example

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 OrderQty column of SalesOrderDetail table.

Max()

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

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

Min()

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

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

Count()

Read: SQL Intersect Operator With Example

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

Returns total number of records in the Person table

String Function Example

LTRIM()

Removes space from left side of the string

select ltrim(' tes')

RTRIM()

Removes space from 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

Examples of Date function

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.

Read: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

MONTH()

select MONTH('12/18/2019')

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

YEAR()

select YEAR('12/18/2019')

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

Advance Function example

IIF()

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

The following query gives out MALE if 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 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 are developed by the user.

Read: What is the Career Path for a SQL Server DBA?

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 Functions 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 statement
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 parameter and gives out the details of 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 [email protected] ;

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

A function can be called from within a function. In this paragraph we will learn how to call a function from within a function.

Here in this example we will call the get date function from within the year function.

Let us see how it works.

Here goes the query statement

select year(getdate())

Here is the output

Few more complicated function example

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

Fibonaccci 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<[email protected])
        	BEGIN
                    	Insert Into @numbers Values(@[email protected])
                    	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)

The output looks like below

Summary

In the above write-up, we have tried to explain, some of the important aspects of SQL Functions. This is not a comprehensive study but will give the reader a fair bit of idea about the different types of SQL Functions and their uses. This introductory write-up about the SQL Blogs will make the readers interested in learning more about this part of the SQL server that is called functions.




    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 14 Jul 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-0 day 10 Jul 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

6 days 16 Jul 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 17 Jul 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

5 days 15 Jul 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

-0 day 10 Jul 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

4 days 14 Jul 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

5 days 15 Jul 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

13 days 23 Jul 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

4 days 14 Jul 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

7 days 17 Jul 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

3 days 13 Jul 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews