Advanced Functions in SQL Server

Learn Advanced Functions In SQL

SQL Functions

  • SQL Server provides various types of functions, including String functions, Numeric functions, and Date functions. 
  • In addition to these, there are several advanced functions that are frequently used, such as IIF, ISNULL, and SYSTEM_USER etc. 

The purpose of this article is to discuss all of the advanced functions available in SQL Server. 

We will create a sample table and use it for demonstrating these functions, using examples. 
 Suppose we have a table called ‘employee’ with columns ‘id’, ‘name’, ‘age’, and ‘salary’. We can create the table using the following SQL statement: 

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT,
  Salary FLOAT
);

Let’s insert some sample data into the “employee” table. 

INSERT INTO employee (Id, Name, Age, Salary)
VALUES (1, ‘John Doe’, 30, 50000.00),
       (2, ‘Jane Smith’, NULL, 60000.00),
       (3, ‘Bob Johnson’, 40, NULL),
       (4, ‘Alice Williams’, NULL, NULL);

 
COALESCE:  
The COALESCE function is used to return the first non-null expression among its arguments. If all arguments are null, then it returns null. 

SELECT COALESCE (NULL, 2, 3);

Result:  

Example: Calculate the average salary, replacing any null values with zero using Employee table. 

SELECT AVG(COALESCE(Salary, 0)) AS Avg_salary
FROM dbo.Employee;

This query will calculate the average salary of all employees in the employee table, replacing any null values with zero. 

Result: 


CURRENT_USER: 

The CURRENT_USER function returns the name of the current user in the SQL Server database. 

SELECT CURRENT_USER;   

 
IIF: 

The IIF function is used to return one value if a condition is true and another value if it is false. 

SELECTIIF(salary > 50000, ‘High’, ‘Low’) FROM dbo.Employee;

Result: 


ISNULL: 

The ISNULL function is used to return the second expression if the first expression is null, otherwise it returns the first expression.  

SELECT ISNULL(Name, ‘Unknown’) FROMdbo.Employee;

Result: 

ISNUMERIC: 

The ISNUMERIC function is used to determine whether an expression is a valid numeric type. 

SELECT ISNUMERIC(‘123.45’); — Returns 1 (True)

Result: 

As you can see in above image, we get 1 as result it means the value ‘123.45’ is numeric. 

Now, let’s use String value: 

SELECTISNUMERIC(‘Jane Smith’); — Returns 0 (False)

Result:

As you can see in above image, we get 0 as result it means the value “Jane Smith” is not a numeric value. 

Example: Select all employees where the age is numeric. 

SELECT Id, Name, Age
FROM dbo.Employee
WHERE ISNUMERIC(Age) = 1;

This query will output the ID, name, and age of all employees in the employee table where the age is numeric

Result: 

Example: Select all employees where the salary is not numeric. 

SELECT Id, Name, Salary
FROM dbo.Employee
WHERE ISNUMERIC(Salary) = 0;

This query will output the ID, name, and salary of all employees in the employee table where the salary is not numeric. 

Result: 

SESSIONPROPERTY: 

The SESSIONPROPERTY function is used to return the value of a specified property for the current session. In other words, The SESSIONPROPERTY function in SQL Server is used to retrieve the value of a specific session-level property. It takes one parameter, which is the name of the session-level property that you want to retrieve. 

Syntax for the SESSIONPROPERTY function is as follows: 

SESSIONPROPERTY(‘property_name’)

The following are some frequently used session-level properties that can be retrieved using the ‘SESSIONPROPERTY’ function in SQL Server. 

Property Name Definition 
ANSI_NULLS Returns the current setting of the ANSI_NULLS option. 
ANSI_PADDING Returns the current setting of the ANSI_PADDING option. 
ANSI_WARNINGS Returns the current setting of the ANSI_WARNINGS option. 
login_name Returns the login name for the current session. 
lcid Returns the language ID for the current session. 
date_format Returns the date format for the current session. 
user Returns the database user for the current session. 

Example:  

SELECT SESSIONPROPERTY(‘ANSI_NULLS’); — Returns 1 (True)

Returns:  


SESSION_USER: 

The SESSION_USER function returns the name of the current user in the current session. 

SELECT SESSION_USER; — Returns the name of the current user in the current session

SYSTEM_USER: 

The SYSTEM_USER function returns the name of the current user in the SQL Server database. 

SELECT SYSTEM_USER; — Returns the name of the current user

USER_NAME: 

The USER_NAME function returns the name of the current user in the SQL Server database. 

SELECT USER_NAME(); — Returns the name of the current user

Interview related question and answer: 

Here are some common interview questions related to the advanced SQL Server functions along with short answers: 

  1. What is the difference between ISNULL and COALESCE? 
    ANSWER: ISNULL and COALESCE are used to return a non-null value if the expression being evaluated is null. The difference is that ISNULL takes only two arguments, while COALESCE can take multiple arguments and returns the first non-null value. 
  1. What is the use of the IIF function? 
    ANSWER: The IIF function is used to return one value if a condition is true, and another value if it is false. 
  1. What is the use of the NULLIF function? 
    ANSWER: The NULLIF function is used to return null if two expressions are equal, otherwise it returns the first expression. 
  1. What is the use of the ISNUMERIC function? 
    ANSWER: The ISNUMERIC function is used to determine whether an expression is a valid numeric type. 
  1. What is the difference between SESSION_USER and SYSTEM_USER? 
    ANSWER: SESSION_USER returns the name of the current user in the current session, while SYSTEM_USER returns the name of the current user in the SQL Server database. 
  1. What is the use of the USER_NAME function? 
    ANSWER: The USER_NAME function returns the name of the current user in the SQL Server database. 
  1. What is the use of the SESSIONPROPERTY function? 
    ANSWER: The SESSIONPROPERTY function is used to return the value of a specified property for the current session. 
  1. What is the difference between CURRENT_USER and SYSTEM_USER? 
    ANSWER: CURRENT_USER and SYSTEM_USER both return the name of the current user in the SQL Server database, but CURRENT_USER returns the name of the current user in the current session and SYSTEM_USER returns the name of the current user in the entire SQL Server instance. 
  1. How can you use COALESCE to concatenate multiple strings? 
    ANSWER: You can use COALESCE to concatenate multiple strings by passing them as arguments to COALESCE, and then using the CONCAT function to join the non-null values returned by COALESCE: 

SELECT CONCAT(COALESCE(string1, ”), COALESCE(string2, ”), COALESCE(string3, ”))
FROM table;

 
10. What is the use of the NULLIF function in division operations? 
ANSWER: The NULLIF function is commonly used in division operations to avoid division by zero errors. If the second argument of the NULLIF function is zero, then it returns null, which prevents the division operation from executing: 

SELECT column1 / NULLIF(column2, 0) FROM table;