SQL Database Functions List – Encyclopedia for SQL Server Functions

SQL Server contains various objects like tables, triggers, stored procedures, functions, etc. Functions in SQL Server are for manipulating the data & extracting useful information from large databases with ease. However, nowadays, there are plenty of functions available & not all users are familiar with all functions.

Evidently, users must learn which function performs what role & when to use it. Therefore, this article consists of 30+ & to be precise a total of 35 functions. Also, we are going to divide these 35 functions based on the categories. Let’s begin with basic information followed by core concepts.

Table of Content

1. Overview
2. Functions Categories
   1. Aggregate
   2. Analytic
   3. Bit Manipulation
   4. Ranking
   5. Rowset
   6. Scaler
3. Recover Deleted Functions
4. Conclusion

What Are SQL Database Functions? Overview

As SQL database has different modes like SQL Database suspect mode, online mode, emergency mode, etc, it has various functions as well. SQL functions are some pre-built actions that users can utilize to get a certain type of result faster. It involves calculation, manipulation of data, etc. The objective of using functions is to automate tasks having a repetitive nature.

The most used functions use the string, numeric, date/time, conditional, aggregate, etc abilities. This way, it becomes easier for users to get the resultant data with pinpoint accuracy.

Types of SQL Server Functions

Before we move ahead, users must understand that these functions are divided into five categories. These categories are:

1. Aggregate Functions
2. Analytic Functions
3. Bit Manipulation Functions
4. Ranking Functions
5. Scaler Functions

Useful Tip: Restore Stored Procedure in SQL Server Database

There are different functions present in all of these categories that users need to focus on. Let’s move further & find out these functions:

SQL Aggregate Functions List with Examples

The aggregation functions are often used for calculating a set of values which returns a single result value. Users can rely on with SELECT statements’ HAVING clause.

1. COUNT(): Counts the number of rows in a result set.

SELECT COUNT(column_name) AS row_count
FROM table_name;

2. SUM(): Calculates the sum of a numeric column.

SELECT SUM(numeric_column) AS total_sum
FROM table_name;

3. AVG(): Computes the average of a numeric column.

SELECT AVG(numeric_column) AS average_value
FROM table_name;

4. MIN(): Finds the minimum value in a column.

SELECT MIN(column_name) AS min_value
FROM table_name;

5. MAX(): Finds the maximum value in a column.

SELECT MAX(column_name) AS max_value
FROM table_name;

Analytic Functions List of SQL Server

Whenever the calculation is based on an aggregate value for a group of rows, it comes under the analytic function. However, as a result, these can return plenty of rows for every group. Some examples are mentioned below:

1. ROW_NUMBER(): Assigns a unique number to each row within a partition of a result set.

SELECT 
    column1, 
    column2,
    ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

2. RANK(): Assigns a rank to each row based on the values in the specified column.

SELECT 
    column1, 
    column2,
    RANK() OVER (ORDER BY column1) AS ranking
FROM table_name;

3. DENSE_RANK(): Similar to RANK(), but without gaps in ranking.

SELECT 
    column1, 
    column2,
    DENSE_RANK() OVER (ORDER BY column1) AS dense_rank
FROM table_name;

4. NTILE(n): Divides an ordered set of rows into “n” number of roughly equal parts.

SELECT 
    column1, 
    column2,
    NTILE(4) OVER (ORDER BY column1) AS quartile
FROM table_name;

5. LEAD(): Accesses data from a subsequent row within the result set.

SELECT 
    column1, 
    column2,
    LEAD(column1) OVER (ORDER BY column1) AS next_value
FROM table_name;

6. LAG(): Accesses data from a previous row within the result set.

SELECT 
    column1, 
    column2,
    LAG(column1) OVER (ORDER BY column1) AS previous_value
FROM table_name;

7. FIRST_VALUE(): Returns the first value in an ordered set.

SELECT 
    column1, 
    column2,
    FIRST_VALUE(column1) OVER (ORDER BY column1) AS first_value
FROM table_name;

8. LAST_VALUE(): Returns the last value in an ordered set.

SELECT 
    column1, 
    column2,
    LAST_VALUE(column1) OVER (ORDER BY column1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM table_name;

Bit Manipulation Functions

When users face a situation where they need to store the data efficiently, they trust the bit manipulation functions. It is way better than getting the results from individual bits.

1. BITAND(): Performs a bitwise AND operation.

SELECT BITAND(5, 3) AS result;

2. BITOR(): Performs a bitwise OR operation.

SELECT BITOR(5, 3) AS result;6

3. BITXOR(): Performs a bitwise XOR operation.

SELECT BITXOR(5, 3) AS result;

4. BITNOT(): Performs a bitwise NOT operation.

SELECT BITNOT(5) AS result;

5. BITCOUNT(): Counts the number of set bits (1s) in a binary expression.

SELECT BIT_COUNT(5) AS result;

The Ranking Functions of SQL Server

Users must consider this factor that ranking functions are nondeterministic in nature. Therefore, these functions return users with a ranking value of each row taken into consideration.

1. RANK(): Assigns a rank to each row based on the values in the specified column.

SELECT 
    column1, 
    column2,
    RANK() OVER (ORDER BY column1) AS ranking
FROM table_name;

2. DENSE_RANK(): Similar to RANK(), but without gaps in ranking.

SELECT
    column1, 
    column2,
    DENSE_RANK() OVER (ORDER BY column1) AS dense_rank
FROM table_name;

3. ROW_NUMBER(): Assigns a unique number to each row within a partition of a result set.

SELECT 
    column1, 
    column2,
    ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

4. NTILE(n): Divides an ordered set of rows into “n” number of roughly equal parts.

SELECT 
    column1, 
    column2,
    NTILE(4) OVER (ORDER BY column1) AS quartile
FROM table_name;

Rowset Functions

Whenever users need to use an object to provide a reference to a table in an SQL statement, they use rowset functions. The examples are given below:

1. OPENQUERY(): Executes a pass-through query on the specified linked server.

SELECT *
FROM OPENQUERY(
   LinkedServerName,
   'SELECT Column1, Column2 FROM RemoteDatabase.RemoteSchema.RemoteTable'
);

2. OPENROWSET(): Provides ad-hoc access to data distributed among heterogeneous data sources.

SELECT *
FROM OPENROWSET(
   'SQLNCLI',
   'Server=RemoteServer;Database=RemoteDatabase;Trusted_Connection=yes;',
   'SELECT Column1, Column2 FROM RemoteSchema.RemoteTable'
);

3. OPENDATASOURCE(): Provides ad-hoc access to remote data sources.

SELECT *
FROM OPENDATASOURCE(
   'SQLNCLI',
   'Data Source=RemoteServer;Integrated Security=SSPI;'
).RemoteDatabase.RemoteSchema.RemoteTable;

Also Read: Difference Between Triggers & Stored Procedures of SQL

SQL Database Scaler Functions

In any scenario, when the expression is valid, users can consider the scaler functions. These use a single value & also return a single-value result.

1. UCASE()/UPPER(): Converts a string to uppercase.

SELECT UCASE(column_name) AS upper_case
FROM table_name;

Or 

SELECT UPPER(column_name) AS upper_case
FROM table_name;

2. LCASE()/LOWER(): Converts a string to lowercase.

SELECT LCASE(column_name) AS lower_case
FROM table_name;

Or 

SELECT LOWER(column_name) AS lower_case
FROM table_name;

3. LEN()/LENGTH(): Returns the length of a string.

SELECT LEN(column_name) AS length
FROM table_name;

Or 

SELECT LENGTH(column_name) AS length
FROM table_name;

4. LEFT()/RIGHT(): Returns a specified number of characters from the left/right of a string.

SELECT LEFT(column_name, 3) AS left_part
FROM table_name;

Or 

SELECT RIGHT(column_name, 3) AS right_part
FROM table_name;

5. LTRIM()/RTRIM(): Removes leading/trailing spaces from a string.

SELECT LTRIM(column_name) AS left_trimmed
FROM table_name;

Or

SELECT RTRIM(column_name) AS right_trimmed
FROM table_name;

6. CONCAT(): Concatenates two or more strings.

SELECT CONCAT(column1, ' ', column2) AS concatenated_result
FROM table_name;

7. SUBSTRING(): Returns a part of a string.

SELECT SUBSTRING(column_name, 1, 3) AS extracted_part
FROM table_name;

8. CHARINDEX(): Returns the starting position of a substring in a string.

SELECT CHARINDEX('substring', column_name) AS position
FROM table_name;

9. COALESCE(): Returns the first non-null expression in a list.

SELECT COALESCE(column1, column2, 'Default') AS result
FROM table_name;

10. CAST()/CONVERT(): It simply converts an expression from one data type to another.

SELECT CAST(column_name AS INT) AS casted_column
FROM table_name;

Or

SELECT CONVERT(INT, column_name) AS converted_column
FROM table_name;

The Final Say

SQL database functions list can help users get the best solution without any hassles. If users are aware of these functions in depth, they can utilize them efficiently. In a nutshell, it makes the entire experience of extracting data & using SQL Server better.

Read More: How to Rebuild Nonclustered Index SQL Server Database