SQL Server Functions

Updated June 15, 2023

SQL Server Functions

SQL server functions are the sets of instructions that are executed to perform a specific task. These functions are the same as a mathematical function, which is used to execute a task, saving time and effort.

There are some specific task and executions which takes time with more computational power; these functions are used in such cases to make it easier, with lower rime complexities. One can enroll for the MySQL certification to master the functionalities.

Key Highlights

• SQL functions are the set of instructions to perform a particular task with less time and effort needed.
• There are many types of SQL functions that are used for many purposes, like numerical, strings, date, and others.
• Scalar-valued SQL functions are the type of SQL function that returns a scalar value as an output.
• Table values SQL functions return a table of the data instead of returning scalar values.

SQL Server Functions

There are some in-built functions in the SQL Server. String, numerical, date, and conversions are one of the most used and popular built functions being used.

String Functions

ASCII

• This function returns the ASCII for the specific character in a string.

Syntax:

SELECT ASCII(Names) AS CodeForFirstChar
FROM Names;

CHAR

• These functions return the character depending upon the ASCII codes.

Syntax:

SELECT CHAR(33) AS CodeChar;

LEFT

• This SQL function returns the characters from a string starting from the left.

Syntax:

SELECT LEFT('SQL', 3) AS String;

RIGHT

• This SQL function returns the characters from the right side of the string.

Syntax:

SELECT RIGHT('SQL', 2) AS String

REVERSE

• This SQL Function takes a string as input and returns the reversed version of the string.

Syntax:

SELECT REVERSE('SQL');

Numeric Functions

ABS

• This SQL function returns the absolute value of the value passed in a function as input.

Syntax

SELECT Abs(-22.5) AS Abs;

AVG

• This SQL function returns the average of the values passed in it.

Syntax:

SELECT AVG(Price) AS Average FROM Products;

COUNT

• This SQL function returns the number of observations in the data column.

Syntax:

SELECT COUNT(ID) AS NUM FROM Products;

LOG

• This SQL function returns the logarithmic value of the input passed in it.

Syntax:

SELECT LOG(22);

SQUARE

• This SQL function returns the square value of the input.

Syntax:

SELECT SQUARE(16);

Date Functions

CURRENT_TIMESTAMP

• This SQL function returns the current date and time.

Syntax:

SELECT CURRENT_TIMESTAMP;

MONTH

• This SQL function returns the current month of the date.

Syntax:

SELECT MONTH(2022/11/21) AS Month;

YEAR

• This SQL function returns the year of the date passed.

Syntax:

SELECT YEAR(2022/11/21) AS Year;

DAY

• This SQL function returns the current date of the date passed.
• Syntax:

SELECT DAY(2022/11/21) AS Day;

GETDATE

• This SQL function returns the date as per the database date and time.
• Syntax:

SELECT GETDATE();

Some Advance Functions

CAST

• This SQL function is used to convert a datatype into a specified datatype mentioned.
• Syntax

SELECT CAST(255.5 AS int);

ISNULL

• This SQL function is used to return a specified value that is not null.
• Syntax:

SELECT ISNULL(NULL, 'Something');

USER_NAME

• This SQL function is used to return a current user name.
• Syntax:

SELECT USER_NAME();

Types of SQL Functions

• When you are working with programming problems, the DRY principle must be followed, which basically means DON’T REPEAT YOURSELF.
• In SQL, there are functions developed like mathematics, using which the time, space, and efforts must be saved, and the same task can be executed with the desired output.
• Mainly, there are two types of SQL functions are used, table-valued and scalar-valued functions.
• The table-valued SQL functions are the type of SQL functions that basically returns the table as an output.
• The scalar value SQL functions are those function that returns scalar values as output like int, varchar, or float.

Scalar Valued SQL Functions

To create a scalar-valued SQL function, we can use CREATE FUNCTION command to create a function.

Syntax:

CREATE FUNCTION sales.NetSale(
    @numbers INT,
    @price DEC(10,2),
    @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS 
BEGIN
    RETURN @numbers * @price * (1 - @discount);
END;

Here in the above code, we can see that we created a SQL function called NetSale, in which we defined 3 parameter numbers, price, and discount. This function takes these 3 values as input and returns an output which is a multiplication of numbers and price and a discount substituted from 1.

Syntax:

SELECT

   sales.NetSale(10,100,0.1) net_sale;

Output: 900

In the above code, we can see that by using SELECT, we are calling a function that we created to pass the values of numbers, prices, and discounts. This function will multiply the values of numbers and prices and the value got from substituting a discount from 1.

Table-valued functions

The table-valued function is the type of SQL function which returns a table as an output. The results from these functions are in the form of a table.

Here to create a table-valued function, we can use CREATE FUNCTION command.

Syntax:

CREATE FUNCTION ProductInYear (
    @year INT
)
RETURNS TABLE
AS
RETURN
    SELECT 
        name,
        year,
        price
    FROM
        production.products
    WHERE
        Year = @year;

In the above code, we can see that we are creating a function called ProductInYear, which will have a model_year parameter and return the values for a particular year.

This function will take a numerical value as a year and check if the same input value is present in the dataset. Let’s suppose we pass 2017 as a year, then the function will check if any data for 2017 is present in model_year and returns all the values matching the 2017 year.

Syntax:

SELECT
    *
FROM
    ProductInYear(2020);

Output: Returns the table of products with the year 2020.

Final Thoughts

In this article, we discussed various SQL functions that can be applied to numerical and categorical data that, included string, numeric, date, and some advanced SQL functions. Further, then we discussed the table-valued and scalar-valued SQL functions with their working mechanisms, SQL query, and logic behind them. Knowledge about these SQL functions and their core intuitions will help one to understand the functions conceptually and help answer interview questions related to them as well.

Frequently Asked Questions(FAQs)

Q1. Define SQL Server Functions.

Answer: SQL Server functions are a set of statements in SQL that are used to take up certain tasks. These functions are similar to the mathematics functions that are basically used to perform common tasks.

Q2. Differentiate between scalar-valued and true-valued functions.

Answer: The method of implementation is the major difference between scalar-valued and true-valued functions. The scalar-valued function returns a single value, and the true-valued function returns the output in the form of a table as per the selected criteria.

Q3. Write about the rules for creating SQL server functions.

Answer: The rules followed are mentioned below,

• Function name should not start with a special character.
• These are operated only with the SELECT statement.
• TRY and CATCH statements should not be used with the functions.
• These functions use the input parameters only.
• The functions must and should return a value when implemented.

Q4. What are the types of user-defined functions in SQL servers?

Answer: SQL server function have two main categories of user-defined functions. Scalar functions that return a single value as output. Table-valued functions that return the output in the form of a table.

Recommended Articles

In this article, you learned about SQL Server Functions, different types, and some inbuilt functions with syntax. To learn more about the topic you can refer to these articles,

1. SQL Server Versions
2. SQL Server Permission
3. SQL Server Replace
4. SQL Server Data Types