Table of contents
1.
Introduction
2.
Functions in MS SQL
2.1.
Types of functions in MS SQL
3.
System Functions In MS SQL
3.1.
1.) CAST
3.2.
2.) CONVERT
3.3.
3.) ISNULL
3.4.
4.) ISNUMERIC
3.5.
5.) IIF
4.
Frequently Asked Questions
4.1.
What is MS SQL?
4.2.
What is the difference between SQL and MS SQL?
4.3.
What are Functions in MS SQL?
4.4.
What are System Functions in MS SQL?
4.5.
What is the use of CHOOSE function in MS SQL?
5.
Conclusion
Last Updated: Mar 27, 2024

MS SQL Server System Function

Author Rajat Agrawal
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

MS SQL or Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide range of transaction processing, business intelligence, and analytics applications.

In MS SQL Server, functions are database objects that include a set of SQL statements to carry out a specific activity. A function takes parameters as input, performs operations, and returns the outcome. 

Let’s learn about MS SQL server functions in-depth.

Functions in MS SQL

In MS Sql Server, functions are database objects that include a set of SQL statements to carry out a specific activity. A function takes parameters as input, performs operations, and returns the outcome. Functions always return a single value or table, which is important to remember. Functions' primary goal is to make it simple to repeat a frequent task. Depending on our needs, we can create functions once and use them in many places. 

Types of functions in MS SQL

There are two types of functions in MS SQL:-

1.) System Functions: Functions that are defined by the system are known as system functions.

2.) User-Defined Functions: The functions that are defined by users according to their preferences are known as system functions.

Let’s learn about some of the common system functions provided by MS SQL.

System Functions In MS SQL

System functions are referred to as functions that are defined by the system. In other words, system functions refer to all the built-in features that the server supports. While performing the given task, the built-in features help us save time.

Let’s learn about the five most commonly used System Functions.

1.) CAST

It will convert a value (of any type) into a specified datatype.

Syntax:

CAST(expression AS datatype(length))

Examples:

1.) Convert a value to an int datatype:

SELECT CAST(50.87 AS int);

Output: 50

The CAST function will convert the decimal number(50.87) into an integer(50). 

2.) Convert a value to a datetime datatype:

SELECT CAST('2022-07-1' AS datetime);

Output: 2022-07-01 00:00:00.000

Here, the CAST function converts varchar into datetime.

2.) CONVERT

It will convert a value (of any type) into a specified datatype.

Syntax:

CONVERT(data_type(length), expression, style)

Examples:

1.) Convert a value to int data type:

SELECT CONVERT(int, 30.54);

Output: 30

The CONVERT function will convert the decimal number(30.54) into an integer(30).

2.) Convert a value to a datetime datatype:

SELECT CONVERT(datetime, '2022-07-1');

Output: 2022-07-01 00:00:00.000

Here, the CONVERT function converts varchar into datetime.

3.) ISNULL

It returns a specified value if the expression is NULL.

Syntax:

ISNULL(expression, value)

Examples:

1.) Return the specified value if the expression is NULL; else, return the expression:

SELECT ISNULL('Coding Ninjas', 'Hello');

Output: Coding Ninjas

Since the expression passed in the 1st argument of ISNULL is not a NULL value; therefore, it returned the expression itself.

2.)  Return the specified value if the expression is NULL; else, return the expression:

SELECT ISNULL(NULL, 'Ninjas');

Output: Ninjas

Here, we can see that the expression is NULL; therefore, it will return the value passed in the 2nd argument as output.

4.) ISNUMERIC

It will test whether an expression is numeric.

Syntax:

ISNUMERIC(expression)

Examples:

1.) Tests whether the expression is numeric:

SELECT ISNUMERIC(10*5);

Output: 1

Since the expression passed in the ISNUMERIC() function is a numeric value, it returns 1.

2.) Tests whether the expression is numeric:

SELECT ISNUMERIC('Coding Ninjas!');

Output: 0

Since the expression passed in the ISNUMERIC() function is not a numeric value, it returns 0.

5.) IIF

It returns a value if a condition is TRUE, or another value if a condition is FALSE.

Syntax:

IIF(condition, value_if_true, value_if_false)

Examples:

1.) Return 50 if the condition is TRUE, or 100 if the condition is FALSE:

SELECT IIF(250<500, 50, 100);

Output: 50

Since the 250<500 condition is TRUE; therefore the output is 50.

2.) Return 'YES'  if the condition is TRUE, or 'NO' if the condition is FALSE:

SELECT IIF('Coding' = 'Ninjas', 'YES', 'NO');

Output: NO

Since the condition is FALSE; therefore the output is NO.

Also Read - TCL Commands In SQL and SQL Clauses

Frequently Asked Questions

What is MS SQL?

MS SQL or Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide range of transaction processing, business intelligence, and analytics applications.

What is the difference between SQL and MS SQL?

The main difference between SQL and MS SQL is that SQL is a query language used in relational databases, whereas MS SQL Server is a relational database management system (RDBMS) created by Microsoft.

What are Functions in MS SQL?

In MS SQL Server, functions are database objects that include a set of SQL statements to carry out a specific activity. A function takes parameters as input, performs operations, and returns the outcome.

What are System Functions in MS SQL?

System functions are referred to as functions that are defined by the system. In other words, system functions refer to all the built-in features that the server supports. While performing the given task, the built-in features help us save time.

What is the use of CHOOSE function in MS SQL?

It returns one of the two values based on the result of the first argument.

Conclusion

In this article, we have extensively discussed Functions, System Functions in MS SQL, and the different built-in system functions provided by MS SQL. If you want to learn more, check out our articles on SQL Server User-Defined FunctionsData Warehousing Tools, and Chinese Support In RediSearch.

Do upvote our blog to help other ninjas grow.

Happy Coding!

Live masterclass