SQL Server Functions Basic | How Many SQL Server Functions are there?

SQL Server functions are a valuable addition to T-SQL (Transact-SQL) when used wisely. a function, in any programming environment, let’s you encapsulate reusable logic and build software that is “compostable”. function hides the steps and the complexity from the code, for example– build of pieces that can be reused and put together in a number of different ways to meet the needs of the users.

therefore, SQL server functions are fundamentally different from functions in other programming environments. In procedural programming, furthermore, the piece of functionality that most programmers call a function should really be called s subroutine, which is more like a miniature program. these subroutines can go about changing data, introducing side effects.

therefore, in SQL Server, functions are more closely to their mathematic definition of mapping, which is the set of inputs to a set of outputs. SQL Server functions accept parameters, and these functions perform some sort of action and return a result. furthermore, they do all of this with no side effects. in the same way as subroutines, SQL Server functions can hide complexity from users and turn a complex piece of code into a reusable commodity. therefore, functions make it possible, let’s suppose, for example, to create very complex search conditions that would be difficult and tedious to express in inline Transact-SQL (T-SQL).

Where Can I Use A Function?

You can use the function “Anywhere”, that we would use a column or table, furthermore, we can use a function anywhere that we can use a table or scalar value. also, their function can be used in constraints, joins, WHERE clauses, computed columns, or even in other functions. therefore, we can say that functions are an incredibly powerful part of SQL Server.

What are Built-In Functions (SQL Server)?

Build-In functions are used in SQL SELECT expressions to manipulate data and calculate values. therefore, these functions can be used anywhere expressions are allowed. there are the common uses of functions include changing a name to all upper case. In SQL a build-in function is a piece for programming that takes zero or more inputs and returns a value.

Note: There are many things to notice relating to functions, let’s see the inputs to a function known as parameters, Not all function has parameters, and a few functions have over one.
the parameters are surrounded (enclosed) in parenthesis.
therefore, we can use a function in the SELECT clause as well as the WHERE filter condition. after that, a function will be used anyplace in a SELECT statement that you just will use an expression.
furthermore, functions are reserved words. I would avoid using them as table names or columns. If you want to do, then expect to qualify your names with brackets [].

A few useful Built-in Functions

there are some of the built-in scalar functions that I’ve frequently found useful.

COALESCE

therefore, the COALESCE takes an unlimited list of arguments and returns the first non-NULL expression. One of the advantages of COALESCE is that it can be used to replace lengthy CASE statements.

SELECT  CASE WHEN col_1 IS NOT NULL THEN col_1
             WHEN col_2 IS NOT NULL THEN col_2
             WHEN col_3 IS NOT NULL THEN col_3
        END AS result

SELECT CASE WHEN col_1 IS NOT NULL THEN col_1

WHEN col_2 IS NOT NULL THEN col_2

WHEN col_3 IS NOT NULL THEN col_3

END AS result

The above code can be simplified by using COALESCE.

SELECT  COALESCE(col_1, col_2, col_3) AS result

1	SELECT COALESCE(col_1, col_2, col_3) AS result

DATEADD and DATEDIFF

To modify date and time values is by using the DATEADD and DATEDIFF functions. The DATEADD function can be used for addition or subtraction an interval to part of a date.

SELECT  DATEADD(hh, 5, GETDATE()) ;
SELECT  DATEADD(hh, -5, GETDATE()) ;
SELECT  DATEADD(dd, 1, GETDATE()) ;

SELECT DATEADD(hh, 5, GETDATE()) ;

SELECT DATEADD(hh, -5, GETDATE()) ;

SELECT DATEADD(dd, 1, GETDATE()) ;

The DATEDIFF the function can be used to calculate the difference between to dates. DATEDIFF is similar to DATEADD – DATEDIFF gets the difference between two dates using the given time interval (year, months, seconds, and so on).

SELECT  DATEDIFF(dd, '2015-03-05', GETDATE()) ;
SELECT  DATEDIFF(hh, '2016-03-03 08:37:00', '2016-04-01 18:54:25') ;

1 2	SELECT DATEDIFF(dd, '2015-03-05', GETDATE()) ; SELECT DATEDIFF(hh, '2016-03-03 08:37:00', '2016-04-01 18:54:25') ;

Therefore, this is the one practical use of the DATEDIFF function is to find the beginning of the current day or month.

SELECT  DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS beginning_of_day ;
SELECT  DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS beginning_of_month ;

1 2	SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS beginning_of_day ; SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AS beginning_of_month ;

therefore, This approach to getting the beginning of the day computes the number of days since the dawn of SQL Server time (January 1, 1900). We then added that the number of days to the dawn of SQL Server time, furthermore, we now have to begin the current hour, day, month, or even year.

SIGN

SIGN returns +1, 0, or -1 which is based on the expression supplied.

SELECT  SIGN(11 - 15)

1	SELECT SIGN(11 - 15)

STUFF

therefore, the STUFF is a powerful built-in function. It inserts one string into another. furthermore, In addition, it also removes a specific number of characters from one string and adds the second string in place of the removed characters. That isn’t a very clear explanation, so let’s see the following example.

SELECT  STUFF('the junk goes here', 2, 5, 'STUFF')
 /*
---------------
STUFF goes here
*/

SELECT STUFF('the junk goes here', 2, 5, 'STUFF')

---------------

STUFF goes here

therefore, the STUFF function can be combined with several XML (extensible markup language) functions to create a comma-separated list of values from a table.

SELECT  STUFF(( SELECT  ',' + Name AS [text()]
                FROM    Production.Culture
              FOR
                XML PATH('')
              ), 1, 1, '') AS cultures ;

SELECT STUFF(( SELECT ',' + Name AS [text()]

FROM Production.Culture

FOR

XML PATH('')

), 1, 1, '') AS cultures ;

The Functions can be Table-valued or Scalar

The Scalar function returns a single value, it doesn’t matter what type it is, as long as it’s only a single, value instead of a table value. You can use a scalar function “anyplace that a scalar expression of the equivalent data type is allowed in T-SQL statements”.
therefore, in addition to user-defined scalar functions, SQL Server provides numerous types of built-in scalar functions, a number of that we’ll cover in more additional detail later. as an example, there are many built-in date functions, such as GETDATE, string functions, such as SUBSTRING, and so on, all of that act on a single value and return a single value. therefore, there are also aggregate functions that perform a calculation on a collection (set of) value and return a single value, as well as a few ranking functions that produce one row for every input row.

furthermore, the Table-valued functions (TVFs) return a table instead of a single value. A table-valued function will be used anywhere a table will be used – usually within the FROM clause of a query. TVF build it possible to encapsulate complicated logic in a query. therefore, as an example, security permissions, calculations, and business logic will be embedded in an exceedingly TVF. Careful use of TVFs makes it simple to create re-usable code frameworks in the database.

Differences b/w scalar and Table-valued functions

the differences between scalar functions and Table-valued functions is that the means during which they can be handled internally, by the SQL Server query optimizer.

therefore, in many places where the function is called, the compiler will automatically incorporate the whole body of the function into the surrounding code.
Most developers will be used to working with compilers that will “inline” trivial function calls.
The alternative is that a function is treated as interpreted code, and invoking it from the main body of code requires a jump to a different code block to execute the function.

Therefore, in some cases, it might be necessary to dispense with the TVF altogether, and “manually inline” the function logic into the main code. furthermore, of course, this defeats the purpose of creating a function to encapsulate reusable logic.

furthermore, the biggest drawback of SQL Server functions is that they will not be automatically inlined. For a scalar function that operates on multiple rows, SQL Server can execute the function once for each row within the result set. This will have an enormous performance impact, as will be demonstrated later within the article.

therefore, with Table-valued functions, SQL Server can decision them one time, no matter the number of rows within the result set and it’s often possible, with a bit of ingenuity, to rewrite scalar functions into Table-valued functions, then avoid the row-by-row process that’s inherent with scalar functions.

Using Sort Order in CLR (Common Language Runtime) Table-valued Functions

There are the following guidelines for when we are using the ORDER clause in CLR table-valued functions:-

You must make sure that results are always ordered in the specified order. therefore, If the results are not in the specified order, SQL Server it can generate an error message when the query is executed.
If an ORDER clause is specified, the output of the table-valued function should be sorted consistent with the collation of the column (explicit or implicit). as an example, if the column collation is Chinese (either specified in the DDL for the table-valued function or obtained from the database collation), the returned results should be sorted according to Chinese sorting rules.
Therefore in the ORDER clause, if specified, it is always verified by SQL Server whereas returning results, whether or not it is used by the query processor to perform any optimizations. Only use the ORDER clause if you recognize it’s useful to the query processor.
The SQL Server query processor takes more advantage of the ORDER clauseautomatically within the following cases:
Insert queries are used where the ORDER clause is compatible with an index.
The ORDER BY clauses that are compatible with the ORDER clause.
Aggregates, where GROUP BY is compatible with the ORDER clause.
The DISTINCT aggregates are used where the distinct columns are compatible with the ORDER clause.

SQL Server Functions can be Deterministic or Nondeterministic

Therefore, a deterministic function will return the same result when it is called with an identical set of input parameters. Adding 2 numbers together is an example of a deterministic function.

A non-deterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different. The GETDATE function, for example, is nondeterministic. furthermore, one caveat of almost all non-deterministic functions is that they are executed once per the statement, not once per row.

Therefore, If you query 50,000 rows of data and use the RAND function to attempt to provide a random value for each row you will be disappointed, therefore, SQL Server will only generate one random number for the whole statement. The only exception to this rule is NEWID, which is able to generate a new GUID for each row within the statement.

Therefore, when we create a function, then SQL Server can analyze the code we’ve created and evaluated whether the function is deterministic. furthermore, If our function makes calls to any non-deterministic functions, it will, itself, be marked as non-deterministic.

Furthermore, The SQL Server relies on the author of a SQL CLR (SQL Common Language Runtime) function to declare the function as deterministic using an attribute. so, the deterministic functions, we can be used in Computed columns and indexed views whereas non-deterministic functions cannot.

Table-valued Functions (TVFs)

Table-valued Functions (TVFs) differ from scalar functions. therefore Table-valued Functions return an entire table whereas scalar functions only return a single value. This makes them ideal for encapsulating more complex logic or functionality for easy re-use. therefore, TVFs have the additional advantage of being executed once to return a large number of rows (as opposed to scalar functions which must be executed many times to return many values).

furthermore, the body of a TVF can either contain just multiple statements or a single statement, but the two cases are handled differently by the optimizer. therefore, If the function body contains just a single statement means that the often referred to as an “inline TVF”, then the optimizer treats it in a similar fashion to a view in that it will “decompose” it and simply reference the underlying objects, therefore, means that there will be no reference to the function in the resulting execution plan.

therefore, however, by contrast, multi-statement Table-valued functions present an optimization problem for SQL Server; it doesn’t know what to do with them. therefore, It treats them rather like a table for which it has no available statistics – the optimizer assumes that the Table-valued functions will always return one row. therefore, as a result, even a very simple multi-statement Table-valued Functions can cause severe performance problems.

SQL Server Function Categories

There is SQL Server, which has hundred built-in functions, we have provided a list of all SQL Server (Transact-SQL) functions. the list of SQL server functions is stored into the type of function, which is based on categories such as string, advanced, conversion, mathematical/numeric, and date/time functions.

furthermore, these functions are used in SQL Server for SQL statements or queries and they can be used within the programming environment provided by the SQL Server (Transact-SQL) database, like functions, stored procedures, triggers, etc.
the given below list of SQL Server functions, sorted by category (i.e type of function).

There are the following SQL Server Functions

String Functions
Date/Time Functions
Numeric/Math Functions
Configuration Function
Conversion Function
Advanced Function

String Functions

ASCII	CHARINDEX	CHAR	LTRIM
LEFT	LOWER	UPPER	LEN
CONCAT	Concat with +	DATALENGTH	NCHAR
PATINDEX	REPLACE	RIGHT	RTRIM
SPACE	STR	STUFF	SUBSTRING

Date/Time Functions

DATEADD	DATEADD	DATENAME	DATEPART
GETUTCDATE	CURRENT_TIMESTAMP	DATEDIFF	GETDATE
DAY	MONTH	YEAR

Numeric/Math Functions

ABS	COUNT	MAX	MIN
RAND	ROUND	AVG	SIGN
SUM	CEILING	FLOOR

Configuration Functions

@@VERSION

Conversion Functions

CONVERT	TRY_CONVERT
TRY_CAST	CAST

Advanced Functions

COALESCE	CURRENT_USER	ISDATE
USER_NAME	SYSTEM_USER	ISNUMERIC
SESSIONPROPERTY	LEAD	SESSION_USER
ISNULL	NULLIF	LAG
CASE