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 sql 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 Functions?
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
Difference 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.
SQL Server Training & Certification
- Personalized Free Consultation
- Access to Our Learning Management System
- Access to Our Course Curriculum
- Be a Part of Our Free Demo Class
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.
SQL Server Course
Upcoming Batches
Trending Courses
Cyber Security
- Introduction to cybersecurity
- Cryptography and Secure Communication
- Cloud Computing Architectural Framework
- Security Architectures and Models
Upcoming Class
-1 day 02 Nov 2024
QA
- Introduction and Software Testing
- Software Test Life Cycle
- Automation Testing and API Testing
- Selenium framework development using Testing
Upcoming Class
9 days 12 Nov 2024
Salesforce
- Salesforce Configuration Introduction
- Security & Automation Process
- Sales & Service Cloud
- Apex Programming, SOQL & SOSL
Upcoming Class
-1 day 02 Nov 2024
Business Analyst
- BA & Stakeholders Overview
- BPMN, Requirement Elicitation
- BA Tools & Design Documents
- Enterprise Analysis, Agile & Scrum
Upcoming Class
1 day 04 Nov 2024
MS SQL Server
- Introduction & Database Query
- Programming, Indexes & System Functions
- SSIS Package Development Procedures
- SSRS Report Design
Upcoming Class
-1 day 02 Nov 2024
Data Science
- Data Science Introduction
- Hadoop and Spark Overview
- Python & Intro to R Programming
- Machine Learning
Upcoming Class
-1 day 02 Nov 2024
DevOps
- Intro to DevOps
- GIT and Maven
- Jenkins & Ansible
- Docker and Cloud Computing
Upcoming Class
-1 day 02 Nov 2024
Hadoop
- Architecture, HDFS & MapReduce
- Unix Shell & Apache Pig Installation
- HIVE Installation & User-Defined Functions
- SQOOP & Hbase Installation
Upcoming Class
5 days 08 Nov 2024
Python
- Features of Python
- Python Editors and IDEs
- Data types and Variables
- Python File Operation
Upcoming Class
6 days 09 Nov 2024
Artificial Intelligence
- Components of AI
- Categories of Machine Learning
- Recurrent Neural Networks
- Recurrent Neural Networks
Upcoming Class
-1 day 02 Nov 2024
Machine Learning
- Introduction to Machine Learning & Python
- Machine Learning: Supervised Learning
- Machine Learning: Unsupervised Learning
Upcoming Class
12 days 15 Nov 2024
Tableau
- Introduction to Tableau Desktop
- Data Transformation Methods
- Configuring tableau server
- Integration with R & Hadoop
Upcoming Class
5 days 08 Nov 2024