Let us discuss about some very important SQL Server system functions in this article. We will talk about how to perform the tasks such as data type conversions, handling the null values, extracting specific parts of date or time values, calculating the string lengths, performing the aggregate calculations, and extracting the substrings. After understanding these SQL Server system functions, it will help us to optimize the database operations and extract useful information from our data.

Create a Sample Table for Demonstration

Let us create a sample table named as “EmP” and insert it with some values to understand the practical usage of the SQL Server system functions. Let’s first create a sample table called “EmP” with columns such as BatchID, Fname, LName, BirthDate, and Salary.

Output:

Types of System Functions in SQL Server

SQL Server provides an extensive library of system functions that serve to diverse data manipulation and analysis requirements. These functions are built-in features provided by the SQL Server and are available for use. Let us discuss about some very important SQL Server system functions that are used frequently.

1. CAST and CONVERT Functions

The CAST and CONVERT functions enable the data type conversion.

Output:

Here in the T-SQL query, the CAST function converts the “Salary” column to an integer. The CONVERT function formats the “BirthDate” column as a string using the format 106 (“dd month yyyy”).

2. ISNULL Function

This function replaces the NULL values with a specified value to ensure the consistent handling of nulls in query results.

Output:

Here in the T-SQL query, the ISNULL function checks the “ProductName” column for NULL values. If a NULL value is found, it replaces it with “N/A”, providing a more meaningful output.

3. DATEPART and DATENAME Functions

These functions extract specific parts of a date or time value such as year, month, or day.

Suppose we need to extract our employee “birth year” and their “birth month”. So, we need to write the following T-SQL query:

Output:

Here in the T-SQL query, the DATEPART function extracts the year from the “BirthDate” column, while the “DATENAME” function extracts the month name. This allows us for easy analysis and grouping of employees’ birthdates.

4. LEN Function

The LEN function in SQL server calculates the length of a string.

Output:

Here in the T-SQL query, the LEN function calculates the length of the “Fname” column, providing the number of characters in each employee’s first name.

5. Aggregate Functions

Some useful aggregate functions in SQL Server are AVG, SUM, COUNT, MIN, and MAX. Let us discuss about each using the T-SQL query.

Output:

Here in the T-SQL query, these aggregate functions calculate the various statistics on the “Salary” column including the average salary, total salary, total number of employees, minimum salary, and maximum salary.

6. SUBSTRING Function

The SUBSTRING function in SQL Server extracts only a portion of a string. Here, we extract only the first three letters from the “Fname” column.

Output:

Here in the T-SQL query, the SUBSTRING function extracts the first three characters from the “Fname” column, providing the reduced versions of each employee’s first name.

With the “Employees” table and these system methods in MS SQL, we may effectively manipulate, analyse, and modify the data. It makes the database maintenance and query operations more efficient. These features expand the SQL Server’s capabilities and versatility, making it a solid option for applications that rely on data.

Conclusion

The SQL Server system functions play a crucial role in data manipulation and analysis within the Microsoft SQL Server. We can perform various operations using functions such as CAST, CONVERT, ISNULL, DATEPART, DATENAME, LEN, and aggregate. These functions improve the functionality and efficiency of SQL Server, and make it a powerful tool for managing the data effectively.