Do you think IIT Guwahati certified course can help you in your career?
No
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.
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.