SQL Functions

Predefined and reusable code blocks.

Introduction

Functions in Microsoft SQL Server (MS SQL) are predefined, reusable code blocks that perform specific operations on data and return a result. SQL Server has a wide range of functions, categorized into different types based on their use, such as scalar functions, aggregate functions, string functions, date and time functions, mathematical functions, and more.

Types of SQL Functions

Functions can be categorized into several types, such as:

  • Scalar Functions: Operate on a single value and return a single value.
  • Aggregate Functions: Operate on a set of values and return a single summarized value.
  • String Functions: Manipulate string data types.
  • Date and Time Functions: Operate on date and time values.
  • Mathematical Functions: Perform mathematical calculations.

Understanding how to use these functions is essential for performing data manipulation and analysis effectively.

In this section, we will discuss the basic types of functions available in MS SQL. 

Scalar Functions

Scalar functions operate on a single value and return a single value.

Common Scalar Functions

  • UPPER(column_name) / LOWER(column_name): It is used to convert the string to upper or lower case.
  • LEN(column_name): It returns the length of the string.
  • ROUND(column_name, decimal_places): It rounds off a numeric value to the specified number of decimal places.
  • ABS(column_name): It returns the absolute value of a number.
  • COALESCE(column1, column2, ...): It returns the first non-null value from the specified columns and it is one of the most important functions to handle NULL values.

Aggregate Functions

Unlike scalar functions, aggregate functions perform a calculation on a set of values and return a single value. They are commonly used with the GROUP BY clause to summarize data.

Common Aggregate Functions

  • COUNT(column_name): Returns the count of non-null values.
  • SUM(column_name): Returns  the total sum of the values.
  • AVG(column_name): Returns  the average value.
  • MAX(column_name): Returns  the maximum value.
  • MIN(column_name): Returns  the minimum value.

String Functions

String functions allow you to manipulate text data. These functions are useful when cleaning or formatting data, performing search operations, or generating reports.

Common String Functions

  • CONCAT(string1, string2,...): Concatenates two or more strings.
  • SUBSTRING(column_name, start, length): Extracts a substring from a string.
  • TRIM(column_name): Removes leading and trailing spaces from a string.
  • REPLACE(column_name, old_substring, new_substring): Replaces occurrences of a substring with another substring.

Date and Time Functions

Date and time functions help you work with dates and times in your database. These functions can extract specific part of the date (like year or month), perform date arithmetic, or change the format of a date.

Common Date and Time Functions

  • GETDATE(): Returns the current date and time.
  • DATEADD(interval, number, date): Adds a specified number of intervals (e.g., days, months) to a date.
  • DATEDIFF(interval, start_date, end_date): Returns the difference between two dates.
  • YEAR(date) / MONTH(date) / DAY(date): Extracts the year, month, or day from a date.

Mathematical Functions

Mathematical functions provide the capability to perform arithmetic operations and complex calculations directly within SQL queries.

Common Mathematical Functions

  • SQRT(column_name): Returns the square root of a number.
  • POWER(column_name, exponent): Raises a number to the specified power.
  • CEILING(column_name): Rounds up to the nearest integer.
  • FLOOR(column_name): Rounds down to the nearest integer.

Best Practices

  • Use Built-In Functions: built-in functions are better than writing custom logic, as they are optimized for performance.
  • Minimize Use in WHERE Clauses: Avoid using functions on columns in the WHERE clause, as this can degrade performance.
  • Consider Data Type Compatibility: make sure the functions used are compatible with the data types in your queries to avoid errors or unexpected results.
  • Use Aggregate Functions Wisely: Be cautious when using aggregate functions, especially on large datasets, as they can be resource-intensive.

Note: We will learn more about string, date and time, and window funtion in intermediate section .

Conclusion

SQL functions are powerful tools that can execute computations, make complicated data manipulation jobs easier, and improve the readability of your queries. You can write improved and efficient SQL code by becoming proficient with both built-in and user-defined functions. We will learn more about user-defined functions in the following sections.

Asked in Interview

  • What are scalar functions in SQL? Can you name a few examples?
  • Explain the difference between ROUND(), CEILING(), and FLOOR() functions.
  • What are aggregate functions? Can you provide examples?
  • What is the difference between COUNT(*) and COUNT(column_name)?
  • What is the difference between scalar and table-valued functions?
  • What is the difference between scalar functions and aggregate functions in SQL?