---

Types of Functions in SQL Server

 There are two types of Functions in SQL Server, Built-in Function and User-defined Functions. Let us discuss them in brief below.

1). Built-in SQL Functions or System Defined Function

These are defined in advance and invoked when you are installing the SQL Server. They can be either be scalar, aggregate, or numeric, etc. Each function returns some value. How can you use them within your database programs, it completely depends on the choice of programmers.

2). User-defined SQL functions

These functions are defined by the user and designed to perform a specific action for a database app. Here are a few highlighted benefits for the same.

 

1. User-defined Functions allow modular programming where it is generated once and called multiple times during programming.
2. Every time a user-defined function is called, the execution code is saved in the cache that helps in faster execution of function when it is called again.
3. A user-defined function utilizes WHERE clause for reducing the overall size of the code that ultimately results in enhanced network performance

Example of System Functions

/* Advanced Functions */
SELECT USER_NAME();
SELECT SYSTEM_USER;
SELECT SESSION_USER;
SELECT SESSIONPROPERTY('ANSI_NULLS');
SELECT ISNUMERIC(4567);
SELECT NULLIF(25, 25);
SELECT NULLIF('Hello', 'Hello');
SELECT NULLIF('Hello', 'world');
SELECT NULLIF('2017-08-25', '2017-08-25');
SELECT ISNULL(NULL, 'RummanAnsai');
SELECT IIF(500<1000, 'YES', 'NO');
SELECT CONVERT(int, 25.65);
SELECT COALESCE(NULL, NULL, NULL, 'Rumman', NULL, 'Ansari');
SELECT COALESCE(NULL, 1, 2, 'Rumman');
SELECT CAST(25.65 AS int);

/* Date Functions */
SELECT CURRENT_TIMESTAMP;
SELECT DATEADD(year, 1, '2017/08/25') AS DateAdd; /* Add one year to a date, then return the date:*/
SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff; /*Return the difference between two date values, in years:*/
SELECT DATEFROMPARTS(2018, 10, 31) AS DateFromParts; /*Return a date from it's parts:*/ 
SELECT DATENAME(year, '2017/08/25') AS DatePartString; /* Return a specified part of a date: */ 
SELECT DATEPART(year, '2017/08/25') AS DatePartInt; /*Return a specified part of a date:*/ 
SELECT DAY('2017/08/25') AS DayOfMonth; /* Return the day of the month for a date: */
SELECT GETDATE();  /* Return the current database system date and time: */
SELECT GETUTCDATE(); /* Return the current UTC date and time: */ 
SELECT ISDATE('2017-08-25'); /* Check if the expression is a valid date: */
SELECT MONTH('2017/08/25') AS Month; /* Return the month part of a date: */ 
SELECT SYSDATETIME() AS SysDateTime; /* Return the date and time of the SQL Server: */ 
SELECT YEAR('2017/08/25') AS Year; /* Return the year part of a date: */

---