SQL Server Function Summary

1. Aggregate Function
AVG returns the average value of the group's values. NULL values are ignored.
binary_checksum returns the binary check value calculated for the list of rows or expressions in the table. Binary_checksum can be used to detect changes to the row in the table
checksum returns the checksum value calculated on the row in the table or on the Expression list. Checksum is used to generate a hash index
checksum_agg to return the check value of the group's median. NULL values are ignored.
count indicates the number of items in the returned group.
count_big indicates the number of items in the returned group. Count_big is similar to the count function. The only difference between them is their return value: count_big always returns the bigint data type value, while count always returns the int data type value
grouping "is an aggregate function, it generates an additional column. When a row is added using the cube or rollup operator, the output value of the appended column is 1. When the added row is not generated by the cube or rollup, the value of the appended column is 0.
only grouping is allowed in the selection list associated with the group by clause that contains the cube or rollup operator. "
Max returns the maximum value of the expression
min returns the minimum value of the expression.
sum returns the sum of all values in the expression, or only the distinct value is returned. Sum can only be used for numeric columns. NULL values are ignored.
STDev returns the statistical standard deviation of all values in the given expression.
stdevp returns the population statistics standard deviation of all values in the given expression.
var returns the given expression. statistical variance of all values in.
varp returns the statistical variance of filling all values in a given expression.

2. mathematical functions
ABS returns the absolute value of a given numeric expression
ACOs returns the angle value in radians. the cosine of this angle value is a given float expression; this function is also called the arccosine.
asin returns an angle value in radians. the sine of this angle value is a given float expression. It is also called an arc sine.
atan returns an angle value in radians, the tangent of the angle value is the given float expression. It is also called an arc tangent.
atn2 returns the angle value in radians. the tangent of the angle value is between two given float expressions; it is also called arc tangent.
ceiling returns a mathematical function greater than or equal to the smallest integer of the given numeric expression.
CoS returns the given angle (in radians) in the given expression)
cot is a mathematical function that returns the angle specified in the given float expression (in radians)
degrees: When an angle in radians is given, returns the angle in degrees.
exp returns the exponent value of the given float expression.
floor returns the maximum integer less than or equal to the given numeric expression.
log returns the natural logarithm of the given float expression
log10 returns the base-10 logarithm of the given float expression
PI returns the constant value of pI
power returns the given expression multiplied by the specified
radians returns the radian value for the degree value entered in the numeric expression
Rand returns the random float value between 0 and 1
round returns the numeric expression and rounding to the specified length or precision
sign returns the positive (+ 1) of the given expression), zero (0) or negative (-1) Signs
sin returns the given angle (in radians) in an approximate number (float) expression)
Square returns the square of the given expression
SQRT returns the square root of the given expression
tan returns the tangent of the input expression

Iii. Date Functions
Dateadd returns a new datetime value based on a period of time added to the specified date.
Datediff returns the number of date and time boundaries across two specified dates.
Datename returns the string representing the specified date part of the specified date
Datepart returns an integer representing the specified date of the specified date.
Returns an integer that represents the day of the specified date.
Getdate returns the current system date and time in the standard internal format of Microsoft SQL Server with datetime Value
Getutcdate returns the datetime value of the current UTC time (World Time Coordinate or Greenwich Mean Time ).
Returns an integer representing the month of the specified date.
Year returns an integer representing the year of the specified date.

Iv. system functions
App_name: the application that returns the current session Program Name (if the application is configured ).
The case expression calculates the condition list and returns one of multiple possible result expressions (see the PPT document for details)
Cast and convert explicitly convert a certain data type expression to another data type (see the PPT document for details)
Coalesce returns the first non-null expression in its parameter
Collationproperty returns the attribute of the given sorting rule
Current_timestamp returns the current date and time. This function is equivalent to getdate ()
CURRENT_USER: returns the current user. This function is equivalent to user_name ()
Datalength returns the number of bytes occupied by any expression.
@ Error returns the last error of the executed Transact-SQL statement. Code
Fn_helpcollations returns a list Of all sorting rules supported by Microsoft SQL Server 2000.
Fn_servershareddrives returns the name of the shared drive used by the Cluster Server
Fn_virtualfilestats returns the I/O statistics on database files (including log files)
Formatmessage constructs a message from an existing message in sysmessages. Formatmessage is similar to the raiserror statement. However, raiserror immediately outputs the message, and formatmessage returns the edited information for further processing.
Getansinull returns the default null value for the session database.
Host_id: the ID of the workstation.
Host_name returns the name of the workstation.
Ident_current returns the final id value generated for the specified table in any session and in any scope.
Ident_incr returns an increment value (returned in the form of numeric (@ maxprecision, 0). This value is specified when an ID column is created in a table or view with an ID column.
Ident_seed returns the seed value (returned in the form of numeric (@ maxprecision, 0). This value is specified when an ID column is created in a table or view with an ID column.
@ Identity returns the last inserted id value
The Identity (function) is used only in select statements with the into Table clause to insert the ID column to the new table. Although similar, the identity function is not the identity attribute used with create table and alter table. (This function can be applied when automatic traffic is collected)
Isdate: determines whether the input expression is a valid date.
Isnull replace null with the specified replacement value
Isnumeric determines whether the expression is a valid numeric type
Newid creates a unique value of the uniqueidentifier type
Nullif returns a null value if two specified expressions are equal.
Parsename returns the specified part of the object name. The objects that can be retrieved include the object name, owner name, database name, and server name.
Permissions returns a value containing a bitmap, indicating the current user's statement, object, or column permissions.
@ Rowcount returns the number of rows affected by the previous statement.
Rowcount_big returns the number of rows affected by the last statement executed. This function has the same function as @ rowcount, unless rowcount_big returns a bigint type.
Scope_identity returns the last identity value inserted into the identity column in the same scope.
Serverproperty returns the property information about the server instance.
Sessionproperty returns the set option settings of the session
Session_user is a niladic function that allows you to insert the values provided by the system for the user name of the current session to the table if no default value is specified. User Names can also be used in queries and error messages.
Stats_date returns the date of the last update of the specified index statistics.
System_user: returns the current user name.
@ Trancount returns the number of active transactions for the current connection
User_name returns the user database username with the given ID number

V. Character Functions
ASCII returns the ASCII code value of the leftmost character of the character expression.
Char converts the intascii code to a character string function.
Charindex returns the starting position of the specified expression in the string.
Difference returns the soundex value difference between two character expressions by an integer.
Left returns the number of characters in the string starting from the left.
Len returns the number of characters (not bytes) of the given string expression, excluding trailing spaces.
Lower converts uppercase data into lowercase data and returns a character expression.
Returns the character expression after ltrim deletes the starting space.
Nchar is defined according to the Unicode Standard and returns Unicode characters with the given integer code
Patindex returns the starting position of the first occurrence of a pattern in the specified expression. If this pattern is not found in all valid text and character data types, zero is returned.
Replace replaces all the second given string expressions in the first string expression with the third expression
Quotename returns the Unicode string with delimiters. The addition of delimiters can make the input string a valid microsoftsqlserver separator.
Replicate
Reverse returns the reverse of the character expression.
Right returns the number of characters starting from the right side of the string.
Rtrim truncates all trailing spaces and returns a string
Soundex returns a code consisting of four characters (soundex) to evaluate the similarity between two strings.
Space returns a string consisting of repeated spaces.
STR character data converted from numeric data
Stuff deletes characters of the specified length and inserts a group of characters at the specified start point.
Substring returns part of the character, binary, text, or image expression.
Unicode returns the integer of the first character of the input expression according to the definition of the Unicode standard.
Upper returns a character expression that converts lowercase data to uppercase.