Once the data is successfully retrieved from the table, you need to further manipulate the data to obtain useful or meaningful results. These requirements include execution of computation and mathematical operations, conversion of data, resolution of numerical values, combination of values and values within the aggregation range. The following table lists the classes and descriptions of T-SQL functions. Function category function the aggregate function performs the following operations:
Once the data is successfully retrieved from the table, you need to further manipulate the data to obtain useful or meaningful results. These requirements include execution of computation and mathematical operations, conversion of data, resolution of numerical values, combination of values and values within the aggregation range. The following table lists the classes and descriptions of T-SQL functions. Function category function the aggregate function performs the following operations:
Once the data is successfully retrieved from the table, you need to further manipulate the data to obtain useful or meaningful results. These requirements include execution of computation and mathematical operations, conversion of data, resolution of numerical values, combination of values and values within the aggregation range.
The following table lists the classes and descriptions of T-SQL functions.
Function category
Function
Aggregate functions
The operation is to merge multiple values into one value. For example, COUNT, SUM, MIN, and MAX.
Configuration Functions
Is a scalar function that returns information about configuration settings.
Conversion functions
Converts a value from one data type to another.
Encryption Functions
Supports encryption, decryption, digital signature, and digital signature verification.
Cursor Functions
Returns information about the cursor status.
Date and Time Functions
You can change the date and time values.
Mathematical functions
Perform triangle, geometric, and other numeric operations.
Metadata Functions
Returns the attributes of the database and database objects.
Ranking Function
Is a non-deterministic function that returns the ranking value of each row in a partition.
Collection Functions
Returns the row set that can be referenced by a table in a Transact-SQL statement.
Security Functions
Returns information about users and roles.
String Functions
You can change the values of char, varchar, nchar, nvarchar, binary, and varbinary.
System Functions
Operate or report system-level options and objects.
System statistical functions
Returns information about SQL Server performance.
Text and image Functions
You can change the value of text and image.
Function composition
The objective of the function is to return a value. Most functions return a scalar value, which represents a data unit or a simple value. In fact, a function can return any data type, including tables, cursors, and Other types that can return a complete multi-row result set. This chapter does not discuss this depth. Chapter 12th describes how to create and use user-defined functions to return more complex data.
The function has existed for a long time, and its history is longer than that of SQL. In almost all programming languages, function calling methods are the same:
Result = Function ()
In T-SQL, SELECT statements are generally used to return values. If you need to return a value from the query, you can use SELECT as the output operator, instead of using the equal sign:
SELECT Function ()
An argument
For SQL functions, parameters represent placeholders of input variables or values. A function can have any parameter. Some parameters are required, while some parameters are optional. Optional parameters are usually placed at the end of a comma-separated parameter table to facilitate the removal of unnecessary parameters in function calls.
In SQL Server online books or online help systems, optional function parameters are represented in square brackets. In the following CONVERT () function example, the length and style parameters of the data type are optional:
CONVERT (data-type [(length)], expression [, style])
You can simplify it to the following format because we do not discuss how to use data types:
CONVERT (date_type, expression [, style])
According to the above definition, the CONVERT () function can accept two or three parameters. Therefore, the following two examples are correct:
20
20101)
The first parameter of this function is the data type Varchar (20), and the second parameter is another function GETDATE (). The GETDATE () function uses the datetime data type to return the current system date and time. The 2nd parameters in the 3rd statements determine the date style. In this example, 101 refers to the date returned in mm/dd/yyyy format. This chapter will introduce the GETDATE () function in detail later. Even if the function does not contain a parameter or does not require a parameter, you must enter a pair of parentheses when calling this function, for example, the GETDATE () function. Note that when using a function name in the book to reference a function, brackets must be included, because this is a standard form.
Deterministic Functions
Due to the internal working mechanism of the database engine, SQL Server must divide functions into two different groups based on the so-called certainty in the U.S. space. This is not a belief in the new era. It is only related to whether function output results can be predicted based on input parameters or execution. If the output of a function is only related to the value of the input parameter, but not to other external factors, this function is a deterministic function. If the output of a function is based on environmental conditions or an algorithm that generates random or dependent results, this function is non-deterministic. For example, the GETDATE () function is a non-deterministic function because it does not return the same value twice. Why are simple things so complicated? The main reason is that non-deterministic functions and global variables cannot be used in some database programming objects (such as user-defined functions ). This is partly because of the way SQL Server caches and pre-compiles executable objects. For example, ad hoc queries can use any function, but it is important to understand this difference if you plan to build advanced, reusable programming objects.
The following functions are deterministic:
L AVG () (All Aggregate functions are deterministic)
L CAST ()
L CONVERT ()
L DATEADD ()
L DATEDIFF ()
L ASCII ()
L CHAR ()
L SUBSTRING ()
The following functions and variables are non-deterministic:
L GETDATE ()
L @ ERROR
L @ SERVICENAME
L CURSORSTATUS ()
L RAND ()
Use user variables in Functions