RnewMonth End Sale : Flat 30% off ON LIVE CLASSES + 2 FREE SELF-PACED COURSES AS A BONUS! - SCHEDULE CALL Rnew

- SQL Server Blogs -

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.

fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    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 Course

AWS

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

Upcoming Class

5 days 08 Dec 2023

DevOps Course

DevOps

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

Upcoming Class

5 days 08 Dec 2023

Data Science Course

Data Science

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

Upcoming Class

5 days 08 Dec 2023

Hadoop Course

Hadoop

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

Upcoming Class

5 days 08 Dec 2023

Salesforce Course

Salesforce

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

Upcoming Class

0 day 03 Dec 2023

QA Course

QA

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

Upcoming Class

5 days 08 Dec 2023

Business Analyst  Course

Business Analyst

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

Upcoming Class

5 days 08 Dec 2023

MS SQL Server Course

MS SQL Server

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

Upcoming Class

-1 day 02 Dec 2023

Python Course

Python

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

Upcoming Class

6 days 09 Dec 2023

Artificial Intelligence  Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence  Course

Upcoming Class

-1 day 02 Dec 2023

Machine Learning Course

Machine Learning

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

Upcoming Class

12 days 15 Dec 2023

Tableau Course

Tableau

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

Upcoming Class

5 days 08 Dec 2023

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews