SQL Server In-Built Functions

Introduction

A feature is fixed of SQL statements that carry out a particular task. Functions foster code reusability. If you need to time and again write huge SQL scripts to carry out the identical task, you may create a feature that plays that task. Next time as opposed to rewriting the SQL, you may virtually name that feature. A feature accepts inputs withinside the shape of parameters and returns a value. SQL Server comes with a fixed of integrated capabilities that carry out a whole lot of tasks.

Of course, you can create a saved method to organize a fixed of SQL statements and execute them, however, saved techniques can not be known as inside SQL statements. Functions, on the opposite hand, can be. Also, any other difficulty with capabilities is that they've to be known for every row. Therefore, in case you are the usage of capabilities with huge record sets, you may hit overall performance issues.

Let’s work via an easy example.

Create Dummy Data

Let's start by creating some dummy data. We will use this data to create user-defined functions.

CREATE DATABASE schooldb 

CREATE TABLE student(
    ID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    GenderVARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    Marks INT NOT NULL,    
) 

INSERT INTO student 
VALUES (1, 'Rajesh', 'Male', '12-MAR-1993', 80), 
(2, 'Subhash', 'Male', '21-FEB-1991', 90), 
(3, 'Mahesh', 'Male', '07-MAR-1995', 85), 
(4, 'Nirva', 'Female', '22-DEC-1981', 70), 
(5, 'Deep', 'Male', '29-JUL-1993', 60), 
(6, 'Jaydip', 'Male', '03-JAN-1985', 85), 
(7, 'Moin', 'Male', '09-APR-1982', 75), 
(8, 'Satish', 'Male', '16-AUG-1974', 80), 
(9, 'Vishal', 'Male', '11-NOV-1987', 90), 
(10, 'Bhargav', 'Male', '28-OCT-1990', 90);

This script will create the database "schooldb" on your server. The database will have a table with five columns, namely ID, Name, Gender, DOB, and "Marks". The table will also contain 10 fictitious student profiles.

Built-in Functions

As mentioned earlier, SQL Server adds some built-in functionality to any database. To see a list of built-in functions for the schooldb database, use go to Object Explorer -> Databases -> schooldb -> Programmability -> Functions -> System Functions. This gives you the list of all built-in functions, as below

SQL Server In-Built Functions

Within the system functions folder, built-in functions are grouped into various folders based on their functionality. For example, if you open the Date and Time Functions folder as shown above, you'll see all functions related to date and time. Expand a function to see the parameter types and values returned by the function. If you want to learn about DateTime in C#, read Working with DateTime in C#.

Expanding the Datename function, we see that this function accepts two parameters. The first parameter is the "date part" of type varchar and the second parameter is the "expression" which is a parameter of type datetime. This function returns a varchar value.

To see the Datename function in action; let's create a query that selects the student's name and date of birth. When I inserted the dummy records, I included the full date of birth (including year, month, and year) for each student. However, using the Datename function only retrieves the date of birth of the student. Consider the query below.

USE schooldb
 
SELECT Name, DATENAME(YEAR, DOB) AS BirthYear
FROM student
 
Output:

Name     BirthYear
Rajesh	 1993
Subhash	 1991
Mahesh	 1995
Nirva	 1981
Deep	 1993
Jaydip	 1985
Moin	 1982
Satish	 1974
Vishal	 1987
Bhargav	 1990

User Defined Functions

Built-in functions don't always provide the functionality you need. Let's take the "Datename" function that we saw in action in the previous section. Dates come in multiple formats, but what if you want to get the date in a different format? Anything not supported by the Datename function. For example, what if you wanted to get the student's date of birth (DOB) in the format "Friday, March 12, 1993"? There is no built-in function to get the date of birth in this format. To do this, you need to call the Datename function multiple times and rely on string concatenation to get the date in the desired format. Consider the following script that retrieves the date in the format just described.

USE schooldb
 
SELECT
 Name,
  DATENAME(DW, DOB)+ ', '+
  DATENAME(DAY, DOB)+ ' '+
  DATENAME(MONTH, DOB) +', '+
  DATENAME(YEAR, DOB) AS DOB
FROM student

Output:

Name       DOB
Rajesh	   Friday, 12 March, 1993
Subhash	   Thursday, 21 February, 1991
Mahesh	   Tuesday, 7 March, 1995
Nirva	   Tuesday, 22 December, 1981
Deep	   Thursday, 29 July, 1993
Jaydip	   Thursday, 3 January, 1985
Moin	   Friday, 9 April, 1982
Satish	   Friday, 16 August, 1974
Vishal	   Wednesday, 11 November, 1987
Bhargav	   Sunday, 28 October, 1990

There are three types of user-defined functions in SQL Server,

Scalar Functions (Returns A Single Value)
Inline Table Valued Functions (Contains a single TSQL statement and returns a Table Set)
Multi-Statement Table Valued Functions (Contains multiple TSQL statements and returns Table Set)

Create user-defined function

Let's create a function called get_FormattedDate. This function takes a value of type DateTime and returns varchar which is the actual formatted date.

Open a new query window and run the following script.

USE schooldb
GO
 /******Created by Rajesh********/
CREATE FUNCTION get_FormattedDate
 (
 @DateValue AS DATETIME
 )
RETURNS VARCHAR(MAX)
AS
BEGIN
	RETURN
	  DATENAME(DW, @DateValue)+ ', '+
	  DATENAME(DAY, @DateValue)+ ' '+
	  DATENAME(MONTH, @DateValue) +', '+
	  DATENAME(YEAR, @DateValue)
 
END

Here the script starts with the USE schooldb command. This is to create this function in the schooldb database. Next, write a "Go" statement to create a new batch statement. SQL server function declarations always begin with CREATE FUNCTION. Parameters passed to a function are specified within left and right parentheses following the function name.

The above script creates a function "get_FormattedDate" that accepts a parameter @DateValue of type DATETIME. Then the return type of the function is specified, in this case, VARCHAR(MAX). Finally, the BEGIN and END statements define the body of the function. Here, in the body of the get_FormattedDate function, we call the Datename function multiple times to create the formatted date.

To check if this function was actually created, go to Object Explorer -> Databases -> schooldb -> Programmability -> Functions -> Scalar Value Functions. increase. Here you will see your newly created function. Expand a function to see the parameters it takes. See the screenshot below for reference

Let's test the scalar function

USE schooldb
 
SELECT
 Name,
 [dbo].[get_FormattedDate](DOB) as DOB
FROM student

Output

Name      DOB
Rajesh	  Friday, 12 March, 1993
Subhash	  Thursday, 21 February, 1991
Mahesh	  Tuesday, 7 March, 1995
Nirva	  Tuesday, 22 December, 1981
Deep	  Thursday, 29 July, 1993
Jaydip	  Thursday, 3 January, 1985
Moin	  Friday, 9 April, 1982
Satish	  Friday, 16 August, 1974
Vishal	  Wednesday, 11 November, 1987
Bhargav	  Sunday, 28 October, 1990

Create an Inline-table value function

A simple definition of a table-valued function (TVF) can be written as follows: A user-defined function that returns the data type of the table and can also accept parameters. TVFs can be used after the FROM clause of a SELECT statement, so they can be used like tables in queries.

Create FUNCTION [dbo].[get_DOBbyName]
(@Name Varchar(100)
)
RETURNS TABLE
AS
RETURN
(SELECT DOB
 FROM student
 WHERE Name = @Name)

Let's test with an example

SELECT DOB FROM dbo.get_DOBbyName('Rajesh')
Output:
DOB
1993-03-12 00:00:00.000