SQL Server function reference

113 functions from the Microsoft SQL documentation, each with 174 verified examples between them. Every result on every page came out of SQL Server 16.0.4265.3 rather than being copied from the documentation.

Each page also lists the versions and services that actually have the function — SQL Server 2017 or 2022, Azure SQL Database, Managed Instance, Synapse, Fabric. “Does this work on Azure?” is the question a T-SQL reference should answer without being read between the lines.

9 further functions are written and verified and go up next. Some chapters will not: where we have nothing to add to the documentation, a page here would not be worth landing on.

/

String Functions29

ASCII()Returns the ASCII code value of the leftmost character of a character expression.CHAR()Returns the single-byte character with the specified integer code, as defined by the character set and encoding of the default collation of the current database.CHARINDEX()Transact-SQL reference for the CHARINDEX function.CONCAT()This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner.CONCAT_WS()This function returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner, using a string separator.DIFFERENCE()DIFFERENCE returns an integer value measuring the difference between the SOUNDEX values of two different character expressions.LEFT()Returns the left part of a character string with the specified number of characters. Transact-SQL syntax conventions ## Syntax ```syntaxsql LEFT ( character_expression , integer_expression ) ``` ## Arguments *character_expression* Is an expression of character or binary data. *character_expression* can be a constant, variable, or column. *character_expression* can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert *character_expression*. > [!NOTE] > If *string_expression* is of type binary or varbinary, LEFT will perform an implicit conversion to varchar, and therefore will not preserve the binary input. *integer_expression* Is a positive integer that specifies how many characters of the *character_expression* will be returned. If *integer_expression* is negative, an error is returned. If *integer_expression* is type bigint and contains a large value, *character_expression* must be of a large data type such as varchar(max). The *integer_expression* parameter counts a UTF-16 surrogate character as one character. ## Return Types Returns varchar when *character_expression* is a non-Unicode character data type. Returns nvarchar when *character_expression* is a Unicode character data type. ## Remarks When using SC collations, the *integer_expression* parameter counts a UTF-16 surrogate pair as one character. For more information, see Collation and Unicode Support. ## Examples ### A. Using LEFT with a column The following example returns the five leftmost characters of each product name in the `Product` table of the database. ```sql SELECT LEFT(Name, 5) FROM Production.Product ORDER BY ProductID; GO ``` ### B. Using LEFT with a character string The following example uses `LEFT` to return the two leftmost characters of the character string `abcdefg`. ```sql SELECT LEFT('abcdefg',2); GO ``` ``` -- ab (1 row(s) affected) ``` ## Examples: and ### C. Using LEFT with a column The following example returns the five leftmost characters of each product name. ```sql -- Uses AdventureWorks SELECT LEFT(EnglishProductName, 5) FROM dbo.DimProduct ORDER BY ProductKey; ``` ### D. Using LEFT with a character string The following example uses `LEFT` to return the two leftmost characters of the character string `abcdefg`. ```sql -- Uses AdventureWorks SELECT LEFT('abcdefg',2) FROM dbo.DimProduct; ``` ``` -- ab ``` ## Related content LEN()LEN returns the number of characters of the specified string expression, excluding trailing spaces.LOWER()Returns a character expression after converting uppercase character data to lowercase. Transact-SQL syntax conventions ## Syntax ```syntaxsql LOWER ( character_expression ) ``` ## Arguments *character_expression* Is an expression of character or binary data. *character_expression* can be a constant, variable, or column. *character_expression* must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert *character_expression*. ## Return Types varchar or nvarchar ## Examples The following example uses the `LOWER` function, the `UPPER` function, and nests the `UPPER` function inside the `LOWER` function in selecting product names that have prices between $11 and $20. ```sql -- Uses AdventureWorks SELECT LOWER(SUBSTRING(EnglishProductName, 1, 20)) AS Lower, UPPER(SUBSTRING(EnglishProductName, 1, 20)) AS Upper, LOWER(UPPER(SUBSTRING(EnglishProductName, 1, 20))) As LowerUpper FROM dbo.DimProduct WHERE ListPrice between 11.00 and 20.00; ``` ``` Lower Upper LowerUpper -------------------- --------------------- -------------------- minipump MINIPUMP minipump taillights - battery TAILLIGHTS - BATTERY taillights - battery ``` ## Related content LTRIM()LTRIM returns a character string after truncating leading characters.NCHAR()Returns the Unicode character with the specified integer code, as defined by the Unicode standard. Transact-SQL syntax conventions ## Syntax ```syntaxsql NCHAR ( integer_expression ) ``` ## Arguments *integer_expression* When the collation of the database does not contain the Supplementary Character (SC) flag, this is a positive integer from 0 through 65535 (0 through 0xFFFF). If a value outside this range is specified, NULL is returned. For more information about supplementary characters, see Collation and Unicode Support. When the collation of the database supports the SC flag, this is a positive integer from 0 through 1114111 (0 through 0x10FFFF). If a value outside this range is specified, NULL is returned. ## Return Types nchar(1) when the default database collation does not support supplementary characters. nvarchar(2) when the default database collation supports supplementary characters. If the parameter *integer_expression* lies in the range 0 - 0xFFFF, only one character is returned. For higher values, NCHAR returns the corresponding surrogate pair. Do not construct a surrogate pair by using `NCHAR(<High surrogate>) + NCHAR(\<Low Surrogate>)`. Instead, use a database collation that supports supplementary characters and then specify the Unicode codepoint for the surrogate pair. The following example demonstrates both the old style method of constructing a surrogate pair and the preferred method of specifying the Unicode codepoint. ```sql CREATE DATABASE test COLLATE Finnish_Swedish_100_CS_AS_SC; DECLARE @d NVARCHAR(10) = N'𣅿'; -- Old style method. SELECT NCHAR(0xD84C) + NCHAR(0xDD7F); -- Preferred method. SELECT NCHAR(143743); -- Alternative preferred method. SELECT NCHAR(UNICODE(@d)); ``` ## Examples ### A. Using NCHAR and UNICODE The following example uses the `UNICODE` and `NCHAR` functions to print the `UNICODE` value and the `NCHAR` (Unicode character) of the second character of the `København` character string, and to print the actual second character, `ø`. ```sql DECLARE @nstring NCHAR(8); SET @nstring = N'København'; SELECT UNICODE(SUBSTRING(@nstring, 2, 1)), NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1))); GO ``` ``` ----------- - 248 ø (1 row(s) affected) ``` ### B. Using SUBSTRING, UNICODE, CONVERT, and NCHAR The following example uses the `SUBSTRING`, `UNICODE`, `CONVERT`, and `NCHAR` functions to print the character number, the Unicode character, and the UNICODE value of each character in the string `København`. ```sql -- The @position variable holds the position of the character currently -- being processed. The @nstring variable is the Unicode character -- string to process. DECLARE @position INT, @nstring NCHAR(9); -- Initialize the current position variable to the first character in -- the string. SET @position = 1; -- Initialize the character string variable to the string to process. -- Notice that there is an N before the start of the string. This -- indicates that the data following the N is Unicode data. SET @nstring = N'København'; -- Print the character number of the position of the string you are at, -- the actual Unicode character you are processing, and the UNICODE -- value for this particular character. PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value'; WHILE @position <= DATALENGTH(@nstring) BEGIN SELECT @position, NCHAR(UNICODE(SUBSTRING(@nstring, @position, 1))), CONVERT(NCHAR(17), SUBSTRING(@nstring, @position, 1)), UNICODE(SUBSTRING(@nstring, @position, 1)) SELECT @position = @position + 1 END; GO ``` ``` Character # Unicode Character UNICODE Value ----------- ---- ----------------- ----------- 1 K K 75 (1 row(s) affected) ----------- ---- ----------------- ----------- 2 ø ø 248 (1 row(s) affected) ----------- ---- ----------------- ----------- 3 b b 98 (1 row(s) affected) ----------- ---- ----------------- ----------- 4 e e 101 (1 row(s) affected) ----------- ---- ----------------- ----------- 5 n n 110 (1 row(s) affected) ----------- ---- ----------------- ----------- 6 h h 104 (1 row(s) affected) ----------- ---- ----------------- ----------- 7 a a 97 (1 row(s) affected) ----------- ---- ----------------- ----------- 8 v v 118 (1 row(s) affected) ----------- ---- ----------------- ----------- 9 n n 110 (1 row(s) affected) ----------- ---- ----------------- ----------- 10 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 11 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 12 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 13 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 14 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 15 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 16 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 17 NULL NULL (1 row(s) affected) ----------- ---- ----------------- ----------- 18 NULL NULL (1 row(s) affected) ``` ## Related content PATINDEX()PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression, or zero.QUOTENAME()QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid SQL Engine delimited identifier.REPLACE()Transact-SQL reference for the REPLACE function, which replaces all occurrences of a specified string value with another string value.REPLICATE()Repeats a string value a specified number of times. Transact-SQL syntax conventions ## Syntax ```syntaxsql REPLICATE ( string_expression , integer_expression ) ``` ## Arguments *string_expression* Is an expression of a character string or binary data type. > [!NOTE] > If *string_expression* is of type binary, REPLICATE will perform an implicit conversion to varchar, and therefore will not preserve the binary input.REVERSE()Returns the reverse order of a string value. Transact-SQL syntax conventions ## Syntax ```syntaxsql REVERSE ( string_expression ) ``` ## Arguments *string_expression* *string_expression* is an expression of a string or binary data type. *string_expression* can be a constant, variable, or column of either character or binary data. ## Return Types varchar or nvarchar ## Remarks *string_expression* must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert *string_expression*. ## Supplementary Characters (Surrogate Pairs) When using SC collations, the REVERSE function will not reverse the order of two halves of a surrogate pair. ## Examples The following example returns all contact first names with the characters reversed. This example uses the database. ```sql SELECT FirstName, REVERSE(FirstName) AS Reverse FROM Person.Person WHERE BusinessEntityID < 5 ORDER BY FirstName; GO ``` ``` FirstName Reverse -------------- -------------- Ken neK Rob boR Roberto otreboR Terri irreT RIGHT()Returns the right part of a character string with the specified number of characters. Transact-SQL syntax conventions ## Syntax ```syntaxsql RIGHT ( character_expression , integer_expression ) ``` ## Arguments *character_expression* Is an expression of character or binary data. *character_expression* can be a constant, variable, or column. *character_expression* can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert *character_expression*. > [!NOTE] > If *string_expression* is of type binary or varbinary, RIGHT will perform an implicit conversion to varchar, and therefore will not preserve the binary input. *integer_expression* Is a positive integer that specifies how many characters of *character_expression* will be returned. If *integer_expression* is negative, an error is returned. If *integer_expression* is type bigint and contains a large value, *character_expression* must be of a large data type such as varchar(max). ## Return Types Returns varchar when *character_expression* is a non-Unicode character data type. Returns nvarchar when *character_expression* is a Unicode character data type. ## Supplementary Characters (Surrogate Pairs) When using SC collations, the RIGHT function counts a UTF-16 surrogate pair as a single character. For more information, see Collation and Unicode Support. ## Examples ### A: Using RIGHT with a column The following example returns the five rightmost characters of the first name for each person in the database. ```sql SELECT RIGHT(FirstName, 5) AS 'First Name' FROM Person.Person WHERE BusinessEntityID < 5 ORDER BY FirstName; GO ``` ``` First Name ---------- Ken Terri berto Rob (4 row(s) affected) ``` ## Examples: and ### B. Using RIGHT with a column The following example returns the five rightmost characters of each last name in the `DimEmployee` table. ```sql -- Uses AdventureWorks SELECT RIGHT(LastName, 5) AS Name FROM dbo.DimEmployee ORDER BY EmployeeKey; ``` Here is a partial result set. ``` Name ----- lbert Brown rello lters ``` ### C. Using RIGHT with a character string The following example uses `RIGHT` to return the two rightmost characters of the character string `abcdefg`. ```sql SELECT RIGHT('abcdefg', 2); ``` ``` ------- fg ``` ## Related content RTRIM()The RTRIM Transact-SQL function returns a character string after truncating all trailing spaces.SOUNDEX()SOUNDEX returns a four-character code to evaluate the similarity of two strings.SPACE()Returns a string of repeated spaces. Transact-SQL syntax conventions ## Syntax ```syntaxsql SPACE ( integer_expression ) ``` ## Arguments *integer_expression* Is a positive integer that indicates the number of spaces. If *integer_expression* is negative, a null string is returned. For more information, see Expressions (Transact-SQL) ## Return Types varchar ## Remarks To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE. ## Examples The following example trims the last names and concatenates a comma, two spaces, and the first names of people listed in the `Person` table in . ```sql USE AdventureWorks2022; GO SELECT RTRIM(LastName) + ',' + SPACE(2) + LTRIM(FirstName) FROM Person.Person ORDER BY LastName, FirstName; GO ``` ## Examples: and The following example trims the last names and concatenates a comma, two spaces, and the first names of people listed in the `DimCustomer` table in `AdventureWorksPDW2012`. ```sql -- Uses AdventureWorks SELECT RTRIM(LastName) + ',' + SPACE(2) + LTRIM(FirstName) FROM dbo.DimCustomer ORDER BY LastName, FirstName; GO ``` ## Related content STR()The STR Transact-SQL function returns character data converted from numeric data.STRING_ESCAPE()The STRING_ESCAPE Transact-SQL function escapes special characters in texts and returns text with escaped characters.STRING_SPLIT()Transact-SQL reference for the STRING_SPLIT function. This table-valued function splits a string into substrings based on a character delimiter.STUFF()The STUFF function inserts a string into another string.SUBSTRING()The SUBSTRING function returns a portion of a specified character, binary, text, or image expression.TRANSLATE()Returns the string provided as a first argument, after some characters specified in the second argument are translated into a destination set of characters, specified in the third argument.TRIM()Removes the space character or other specified characters from the start and end of a string.UNICODE()Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql UNICODE ( 'ncharacter_expression' ) ``` ## Arguments ' *ncharacter_expression* ' Is an nchar or nvarchar expression. ## Return Types int ## Remarks In versions of earlier than and in , the UNICODE function returns a UCS-2 codepoint in the range 000000 through 00FFFF which is capable of representing the 65,535 characters in the Unicode Basic Multilingual Plane (BMP). Starting with , when using Supplementary Character (SC) enabled collations, UNICODE returns a UTF-16 codepoint in the range 000000 through 10FFFF. For more information on Unicode support in the , see Collation and Unicode Support. ## Examples ### A. Using UNICODE and the NCHAR function The following example uses the `UNICODE` and `NCHAR` functions to print the UNICODE value of the first character of the string `Åkergatan 24`, and to print the actual first character, `Å`. ```sql DECLARE @nstring NCHAR(12); SET @nstring = N'Åkergatan 24'; SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring)); ``` ``` ----------- - 197 Å ``` ### B. Using SUBSTRING, UNICODE, and CONVERT The following example uses the `SUBSTRING`, `UNICODE`, and `CONVERT` functions to print the character number, the Unicode character, and the UNICODE value of each of the characters in the string `Åkergatan 24`. ```sql -- The @position variable holds the position of the character currently -- being processed. The @nstring variable is the Unicode character -- string to process. DECLARE @position INT, @nstring NCHAR(12); -- Initialize the current position variable to the first character in -- the string. SET @position = 1; -- Initialize the character string variable to the string to process. -- Notice that there is an N before the start of the string, which -- indicates that the data following the N is Unicode data. SET @nstring = N'Åkergatan 24'; -- Print the character number of the position of the string you are at, -- the actual Unicode character you are processing, and the UNICODE -- value for this particular character. PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value'; WHILE @position <= LEN(@nstring) -- While these are still characters in the character string,UPPER()Returns a character expression with lowercase character data converted to uppercase. Transact-SQL syntax conventions ## Syntax ```syntaxsql UPPER ( character_expression ) ``` ## Arguments *character_expression* Is an expression of character data. *character_expression* can be a constant, variable, or column of either character or binary data. *character_expression* must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert *character_expression*. ## Return Types varchar or nvarchar ## Examples The following example uses the `UPPER` and `RTRIM` functions to return the last name of people in the `dbo.DimEmployee` table so that it is in uppercase, trimmed, and concatenated with the first name. ```sql -- Uses AdventureWorks SELECT UPPER(RTRIM(LastName)) + ', ' + FirstName AS Name FROM dbo.DimEmployee ORDER BY LastName; ``` Here is a partial result set. ``` Name ------------------------------ ABBAS, Syed ABERCROMBIE, Kim ABOLROUS, Hazem ``` ## Related content

Mathematical Functions15

ABS()A mathematical function that returns the absolute (positive) value of the specified numeric expression. (`ABS` changes negative values to positive values. `ABS` has no effect on zero or positive values.) Transact-SQL syntax conventions ## Syntax ```syntaxsql ABS ( numeric_expression ) ``` ## Arguments ASIN()A function that returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine. Transact-SQL syntax conventions ## Syntax ```syntaxsql ASIN ( float_expression ) ``` ## Arguments *float_expression* An expression of either type float or of a type that can implicitly convert to float. Only a value ranging from -1.00 to 1.00 is valid. For values outside this range, no value is returned, and ASIN will report a domain error. ## Return types float ## Examples This example takes a float expression and returns the ASIN value of the specified angle. ```sql /* The first value will be -1.01. This fails because the value is outside the range.*/ DECLARE @angle FLOAT SET @angle = -1.01 SELECT 'The ASIN of the angle is: ' + CONVERT(VARCHAR, ASIN(@angle)) GO -- The next value is -1.00. DECLARE @angle FLOAT SET @angle = -1.00 SELECT 'The ASIN of the angle is: ' + CONVERT(VARCHAR, ASIN(@angle)) GO -- The next value is 0.1472738. DECLARE @angle FLOAT SET @angle = 0.1472738 SELECT 'The ASIN of the angle is: ' + CONVERT(VARCHAR, ASIN(@angle)) GO ``` ``` ------------------------- .Net SqlClient Data Provider: Msg 3622, Level 16, State 1, Line 3 A domain error occurred. --------------------------------- The ASIN of the angle is: -1.5708 (1 row(s) affected) ---------------------------------- The ASIN of the angle is: 0.147811 (1 row(s) affected) ``` ## Examples: and This example returns the arcsine of 1.00. ```sql SELECT ASIN(1.00) AS asinCalc; ``` This example returns an error, because it requests the arcsine for a value outside the allowed range. ```sql SELECT ASIN(1.1472738) AS asinCalc; ``` ## Related content ATAN()A function that returns the angle, in radians, whose tangent is a specified float expression. This is also called arctangent. Transact-SQL syntax conventions ## Syntax ```syntaxsql ATAN ( float_expression ) ``` ## Arguments *float_expression* An expression of either type float or of a type that implicitly convert to float. ## Return types float ## Examples This example takes a float expression and returns the ATAN of the specified angle. ```sql SELECT 'The ATAN of -45.01 is: ' + CONVERT(varchar, ATAN(-45.01)) SELECT 'The ATAN of -181.01 is: ' + CONVERT(varchar, ATAN(-181.01)) SELECT 'The ATAN of 0 is: ' + CONVERT(varchar, ATAN(0)) SELECT 'The ATAN of 0.1472738 is: ' + CONVERT(varchar, ATAN(0.1472738)) SELECT 'The ATAN of 197.1099392 is: ' + CONVERT(varchar, ATAN(197.1099392)) GO ``` ``` ------------------------------- The ATAN of -45.01 is: -1.54858 (1 row(s) affected) -------------------------------- The ATAN of -181.01 is: -1.56527 (1 row(s) affected) -------------------------------- The ATAN of 0 is: 0 (1 row(s) affected) ---------------------------------- The ATAN of 0.1472738 is: 0.146223 (1 row(s) affected) ----------------------------------- The ATAN of 197.1099392 is: 1.56572 (1 row(s) affected) ``` ## Examples: and This example takes a float expression and returns the arctangent of the specified angle. ```sql SELECT ATAN(45.87) AS atanCalc1, ATAN(-181.01) AS atanCalc2, ATAN(0) AS atanCalc3, ATAN(0.1472738) AS atanCalc4, ATAN(197.1099392) AS atanCalc5; ``` ``` atanCalc1 atanCalc2 atanCalc3 atanCalc4 atanCalc5 --------- --------- --------- --------- --------- 1.55 -1.57 0.00 0.15 1.57 ``` ## Related content CEILING()CEILING returns the smallest integer greater than or equal to the specified numeric expression.COS()A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql COS ( float_expression ) ``` ## Arguments *float_expression* An expression of type float. ## Return types float ## Examples This example returns the `COS` value of the specified angle: ```sql DECLARE @angle FLOAT; SET @angle = 14.78; SELECT 'The COS of the angle is: ' + CONVERT(VARCHAR,COS(@angle)); GO ``` ``` The COS of the angle is: -0.599465 (1 row(s) affected) ``` and EXP()Returns the exponential value of the specified float expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql EXP ( float_expression ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float. ## Return Types float ## Remarks The constant e (2.718281...), is the base of natural logarithms. The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067. The exponential of the natural logarithm of a number is the number itself: EXP (LOG (*n*)) = *n*. And the natural logarithm of the exponential of a number is the number itself: LOG (EXP (*n*)) = *n*. ## Examples ### A. Finding the exponent of a number The following example declares a variable and returns the exponential value of the specified variable (`10`) with a text description. ```sql DECLARE @var FLOAT SET @var = 10 SELECT 'The EXP of the variable is: ' + CONVERT(VARCHAR, EXP(@var)) GO ``` ``` ---------------------------------------------------------- The EXP of the variable is: 22026.5 (1 row(s) affected) ``` ### B. Finding exponentials and natural logarithms The following example returns the exponential value of the natural logarithm of `20` and the natural logarithm of the exponential of `20`. Because these functions are inverse functions of one another, the return value in both cases is `20`. ```sql SELECT EXP(LOG(20)), LOG(EXP(20)) GO ``` ``` ---------------------- ---------------------- 20 20 (1 row(s) affected) ``` ## Examples: and ### C. Finding the exponent of a number The following example returns the exponential value of the specified value (`10`). ```sql SELECT EXP(10); ``` ``` ---------- 22026.4657948067 ``` ### D. Finding exponential values and natural logarithms The following example returns the exponential value of the natural logarithm of `20` and the natural logarithm of the exponential of `20`. Because these functions are inverse functions of one another, the return value in both cases is `20`. ```sql SELECT EXP( LOG(20)), LOG( EXP(20)); ``` ``` -------------- ----------------- 20 20 ``` ## Related content FLOOR()FLOOR returns the largest integer less than or equal to the specified numeric expression.LOG()Returns the natural logarithm of the specified float expression in . Transact-SQL syntax conventions ## Syntax ```syntaxsql -- Syntax for SQL Server, Azure SQL Database LOG ( float_expression [, base ] ) ``` ```syntaxsql -- Syntax for Azure Synapse SQL LOG ( float_expression ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float. *base* Optional integer argument that sets the base for the logarithm. Applies to: and later ## Return Types float ## Remarks By default, LOG() returns the natural logarithm. Starting with , you can change the base of the logarithm to another value by using the optional *base* parameter. The natural logarithm is the logarithm to the base e, where e is an irrational constant approximately equal to 2.718281828. The natural logarithm of the exponential of a number is the number itself: LOG( EXP( *n* ) ) = *n*. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( *n* ) ) = *n*. ## Examples ### A. Calculating the logarithm for a number. The following example calculates the `LOG` for the specified float expression. ```sql DECLARE @var FLOAT = 10; SELECT 'The LOG of the variable is: ' + CONVERT(VARCHAR, LOG(@var)); GO ``` ``` ------------------------------------- The LOG of the variable is: 2.30259 (1 row(s) affected) ``` ### B. Calculating the logarithm of the exponent of a number. The following example calculates the `LOG` for the exponent of a number. ```sql SELECT LOG (EXP (10)); ``` ``` ---------------------------------- 10 (1 row(s) affected) ``` ## Examples: and ### C. Calculating the logarithm for a number The following example calculates the `LOG` for the specified float expression. ```sql SELECT LOG(10); ``` ``` ----------------` 2.30 ``` ## Related content LOG10()Returns the base-10 logarithm of the specified float expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql LOG10 ( float_expression ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float. ## Return Types float ## Remarks The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(*n*) = *n*. ## Examples ### A. Calculating the base 10 logarithm for a variable. The following example calculates the `LOG10` of the specified variable. ```sql DECLARE @var FLOAT; SET @var = 145.175643; SELECT 'The LOG10 of the variable is: ' + CONVERT(VARCHAR,LOG10(@var)); GO ``` ``` The LOG10 of the variable is: 2.16189 (1 row(s) affected) ``` ### B. Calculating the result of raising a base-10 logarithm to a specified power. The following example returns the result of raising a base-10 logarithm to a specified power. ```sql SELECT POWER (10, LOG10(5)); ``` ``` ----------- 5 (1 row(s) affected) ``` ## Examples: and ### C: Calculating the base 10 logarithm for a value. The following example calculates the `LOG10` of the specified value. ```sql SELECT LOG10(145.175642); ``` ``` ------------------- 2.16 ``` ## Related content POWER()Returns the value of the specified expression to the specified power. Transact-SQL syntax conventions ## Syntax ```syntaxsql POWER ( float_expression , y ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float. *y* Is the power to which to raise *float_expression*. *y* can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type. ## Return Types The return type depends on the input type of *float_expression*: |Input type|Return type| |----------|-----------| |float, real|float| |decimal(*p*, *s*)|decimal(38, *s*)| |int, smallint, tinyint|int| |bigint|bigint| |money, smallmoney|money| |bit, char, nchar, varchar, nvarchar|float| If the result does not fit in the return type, an arithmetic overflow error occurs. ## Examples ### A. Using POWER to return the cube of a number The following example demonstrates raising a number to the power of 3 (the cube of the number). ```sql DECLARE @input1 FLOAT; DECLARE @input2 FLOAT; SET @input1= 2; SET @input2 = 2.5; SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2; ``` ``` Result1 Result2 ---------------------- ---------------------- 8 15.625 (1 row(s) affected) ``` ### B. Using POWER to show results of data type conversion The following example shows how the *float_expression* preserves the data type which can return unexpected results. ```sql SELECT POWER(CAST(2.0 AS FLOAT), -100.0) AS FloatResult, POWER(2, -100.0) AS IntegerResult, POWER(CAST(2.0 AS INT), -100.0) AS IntegerResult, POWER(2.0, -100.0) AS Decimal1Result, POWER(2.00, -100.0) AS Decimal2Result, POWER(CAST(2.0 AS DECIMAL(5,2)), -100.0) AS Decimal2Result; GO ``` ``` FloatResult IntegerResult IntegerResult Decimal1Result Decimal2Result Decimal2Result ---------------------- ------------- ------------- -------------- -------------- -------------- 7.88860905221012E-31 0 0 0.0 0.00 0.00 ``` ### C. Using POWER The following example returns `POWER` results for `2`. ```sql DECLARE @value INT, @counter INT; SET @value = 2; SET @counter = 1; WHILE @counter < 5 BEGIN SELECT POWER(@value, @counter) SET NOCOUNT ON SET @counter = @counter + 1 SET NOCOUNT OFF END; GO ``` ``` ----------- 2 (1 row(s) affected) ----------- 4 (1 row(s) affected) ----------- 8 (1 row(s) affected) ----------- 16 (1 row(s) affected) ``` ## Examples: and ### D: Using POWER to return the cube of a number The following example shows returns `POWER` results for `2.0` to the 3rd power. ```sql SELECT POWER(2.0, 3); ``` ``` ------------ 8.0 ``` ## Related content ROUND()ROUND returns a numeric value, rounded to the specified length or precision.SIGN()Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql SIGN ( numeric_expression ) ```SIN()Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql SIN ( float_expression ) ```SQRT()Returns the square root of the specified float value. Transact-SQL syntax conventions ## Syntax ```syntaxsql SQRT ( float_expression ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float. ## Return Types float ## Examples The following example returns the square root of numbers between `1.00` and `10.00`. ```sql DECLARE @myvalue FLOAT; SET @myvalue = 1.00; WHILE @myvalue < 10.00 BEGIN SELECT SQRT(@myvalue); SET @myvalue = @myvalue + 1 END; GO ``` ``` ------------------------ 1.0 ------------------------ 1.4142135623731 ------------------------ 1.73205080756888 ------------------------ 2.0 ------------------------ 2.23606797749979 ------------------------ 2.44948974278318 ------------------------ 2.64575131106459 ------------------------ 2.82842712474619 ------------------------ 3.0 ``` ## Examples: and The following example returns the square root of numbers `1.00` and `10.00`. ```sql SELECT SQRT(1.00), SQRT(10.00); ``` ``` ---------- ------------ 1.00 3.16 ``` ## Related content TAN()Returns the tangent of the input expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql TAN ( float_expression ) ``` ## Arguments *float_expression* Is an expression of type float or of a type that can be implicitly converted to float, interpreted as number of radians. ## Return Types float ## Examples The following example returns the tangent of `PI()/2`. ```sql SELECT TAN(PI()/2); ``` ``` ---------------------- 1.6331778728383844E+16 ``` ## Examples: and The following example returns the tangent of .45. ```sql SELECT TAN(.45); ``` ``` -------- 0.48 ``` ## Related content

Aggregate Functions15

APPROX_COUNT_DISTINCT()This function returns the approximate number of unique non-null values in a group. Transact-SQL syntax conventions ## Syntax ```syntaxsql APPROX_COUNT_DISTINCT ( expression ) ``` ## Arguments *expression* An expression of any type, except image, sql_variant, ntext, or text.AVG()This function returns the average of the values in a group. It ignores null values.CHECKSUM_AGG()This function returns the checksum of the values in a group. `CHECKSUM_AGG` ignores null values. The OVER clause can follow `CHECKSUM_AGG`. Transact-SQL syntax conventions ## Syntax ```syntaxsql CHECKSUM_AGG ( [ ALL | DISTINCT ] expression ) ``` ## Arguments ALL Applies the aggregate function to all values. ALL is the default argument. DISTINCT Specifies that `CHECKSUM_AGG` returns the checksum of unique values. *expression* An integer expression. `CHECKSUM_AGG` does not allow use of aggregate functions or subqueries. ## Return types Returns the checksum of all *expression* values as int. ## Remarks `CHECKSUM_AGG` can detect changes in a table. The `CHECKSUM_AGG` result does not depend on the order of the rows in the table. Also, `CHECKSUM_AGG` functions allow the use of the `DISTINCT` keyword and the `GROUP BY` clause. If an expression list value changes, the list checksum value list will also probably change. However, a small possibility exists that the calculated checksum will not change. `CHECKSUM_AGG` has functionality similar to that of other aggregate functions. For more information, see Aggregate Functions (Transact-SQL). ## Examples These examples use `CHECKSUM_AGG` to detect changes in the `Quantity` column of the `ProductInventory` table in the database. ```sql --Get the checksum value before the column value is changed.COUNT()This function returns the number of items found in a group. `COUNT` operates like the COUNT_BIG function. These functions differ only in the data types of their return values. `COUNT` always returns an int data type value. `COUNT_BIG` always returns a bigint data type value.COUNT_BIG()This function returns the number of items found in a group. `COUNT_BIG` operates like the COUNT function. These functions differ only in the data types of their return values. `COUNT_BIG` always returns a bigint data type value. `COUNT` always returns an int data type value.GROUPING()Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT \<select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. Transact-SQL syntax conventions ## Syntax ```syntaxsql GROUPING ( <column_expression> ) ``` ## Arguments \<column_expression> Is a column or an expression that contains a column in a GROUP BY clause. ## Return Types tinyint ## Remarks GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all. ## Examples The following example groups `SalesQuota` and aggregates `SaleYTD` amounts in the database. The `GROUPING` function is applied to the `SalesQuota` column. ```sql SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping' FROM Sales.SalesPerson GROUP BY SalesQuota WITH ROLLUP; GO ``` The result set shows two null values under `SalesQuota`. The first `NULL` represents the group of null values from this column in the table. The second `NULL` is in the summary row added by the ROLLUP operation. The summary row shows the `TotalSalesYTD` amounts for all `SalesQuota` groups and is indicated by `1` in the `Grouping` column. ``` SalesQuota TotalSalesYTD Grouping ------------ ----------------- -------- NULL 1533087.5999 0 250000.00 33461260.59 0 300000.00 9299677.9445 0 NULL 44294026.1344 1 GROUPING_ID()GROUPING_ID is a function that computes the level of grouping, in a SELECT, HAVING, or ORDER BY clause.MAX()MAX returns the maximum of all values of the specified expression in a group.MIN()MIN returns the minimum of all values of the specified expression in a group.STDEV()Returns the statistical standard deviation of all values in the specified expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql -- Aggregate Function Syntax STDEV ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax STDEV ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) ``` ## Arguments ALL Applies the function to all values. ALL is the default. DISTINCT Specifies that each unique value is considered. *expression* Is a numeric expression. Aggregate functions and subqueries are not permitted. *expression* is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. OVER ( [ *partition_by_clause* ] _order\_by\_clause_) _partition\_by\_clause_ divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. _order\_by\_clause_ determines the logical order in which the operation is performed. _order\_by\_clause_ is required. For more information, see OVER Clause (Transact-SQL). ## Return Types float ## Remarks If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEV can be used with numeric columns only. Null values are ignored. STDEV is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. ## Examples ### A: Using STDEV The following example returns the standard deviation for all bonus values in the `SalesPerson` table in the database. ```sql SELECT STDEV(Bonus) FROM Sales.SalesPerson; GO ``` ## Examples: and ### B: Using STDEV The following example returns the standard deviation of the sales quota values in the table `dbo.FactSalesQuota`. The first column contains the standard deviation of all distinct values and the second column contains the standard deviation of all values including any duplicates values. ```sql -- Uses AdventureWorks SELECT STDEV(DISTINCT SalesAmountQuota)AS Distinct_Values, STDEV(SalesAmountQuota) AS All_Values FROM dbo.FactSalesQuota; ``` ``` Distinct_Values All_Values ---------------- ---------------- 398974.27 398450.57 ``` ### C. Using STDEV with OVER The following example returns the standard deviation of the sales quota values for each quarter in a calendar year. Notice that the ORDER BY in the OVER clause orders the STDEV and the ORDER BY of the SELECT statement orders the result set. ```sql -- Uses AdventureWorks SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota, STDEV(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS StdDeviation FROM dbo.FactSalesQuota WHERE EmployeeKey = 272 AND CalendarYear = 2002 ORDER BY CalendarQuarter; ``` ``` Year Quarter SalesQuota StdDeviation ---- ------- ---------------------- ------------------- 2002 1 91000.0000 null 2002 2 140000.0000 34648.23 2002 3 70000.0000 35921.21 2002 4 154000.0000 39752.36 ``` ## Related content STDEVP()Returns the statistical standard deviation for the population for all values in the specified expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql -- Aggregate Function Syntax STDEVP ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax STDEVP ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) ``` ## Arguments ALL Applies the function to all values. ALL is the default. DISTINCT Specifies that each unique value is considered. *expression* Is a numeric expression. Aggregate functions and subqueries are not permitted. *expression* is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. OVER ( [ _partition\_by\_clause_ ] _order\_by\_clause_) *partition_by_clause* divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. *order_by_clause* determines the logical order in which the operation is performed. *order_by_clause* is required. For more information, see OVER Clause (Transact-SQL). ## Return Types float ## Remarks If STDEVP is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEVP can be used with numeric columns only. Null values are ignored. STDEVP is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. ## Examples ### A: Using STDEVP The following example returns the standard deviation for the population for all bonus values in the `SalesPerson` table in the database. ```sql SELECT STDEVP(Bonus) FROM Sales.SalesPerson; GO ``` ## Examples: and ### B: Using STDEVP The following example returns the `STDEVP` of the sales quota values in the table `dbo.FactSalesQuota`. The first column contains the standard deviation of all distinct values and the second column contains the standard deviation of all values including any duplicates values. ```sql -- Uses AdventureWorks SELECT STDEVP(DISTINCT SalesAmountQuota)AS Distinct_Values, STDEVP(SalesAmountQuota) AS All_Values FROM dbo.FactSalesQuota;SELECT STDEVP(DISTINCT Quantity)AS Distinct_Values, STDEVP(Quantity) AS All_Values FROM ProductInventory; ``` ``` Distinct_Values All_Values ---------------- ---------------- 397676.79 397226.44 ``` ### C. Using STDEVP with OVER The following example returns the `STDEVP` of the sales quota values for each quarter in a calendar year. Notice that the `ORDER BY` in the `OVER` clause orders the `STDEVP` and the `ORDER BY` of the `SELECT` statement orders the result set. ```sql -- Uses AdventureWorks SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota, STDEVP(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS StdDeviation FROM dbo.FactSalesQuota WHERE EmployeeKey = 272 AND CalendarYear = 2002 ORDER BY CalendarQuarter; ``` ``` Year Quarter SalesQuota StdDeviation ---- ------- ---------------------- ------------------- 2002 1 91000.0000 0.00 2002 2 140000.0000 24500.00 2002 3 70000.0000 29329.55 2002 4 154000.0000 34426.55 ``` ## Related content STRING_AGG()STRING_AGG concatenates the values of string expressions and places separator values between them.SUM()SUM returns the sum of all the values, or only the DISTINCT values, in the expression.VAR()Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause. Transact-SQL syntax conventions ## Syntax ```syntaxsql -- Aggregate Function Syntax VAR ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax VAR ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) ``` ## Arguments ALL Applies the function to all values. ALL is the default. DISTINCT Specifies that each unique value is considered. *expression* Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted. OVER ( [ _partition\_by\_clause_ ] _order\_by\_clause_) *partition_by_clause* divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. _order\_by\_clause_ determines the logical order in which the operation is performed. _order\_by\_clause_ is required. For more information, see OVER Clause (Transact-SQL). ## Return Types float ## Remarks If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation. VAR can be used with numeric columns only. Null values are ignored. VAR is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. ## Examples ### A: Using VAR The following example returns the variance for all bonus values in the `SalesPerson` table in the database. ```sql SELECT VAR(Bonus) FROM Sales.SalesPerson; GO ``` ## Examples: and ### B: Using VAR The following example returns the statistical variance of the sales quota values in the table `dbo.FactSalesQuota`. The first column contains the variance of all distinct values and the second column contains the variance of all values including any duplicates values. ```sql -- Uses AdventureWorks SELECT VAR(DISTINCT SalesAmountQuota)AS Distinct_Values, VAR(SalesAmountQuota) AS All_Values FROM dbo.FactSalesQuota; ``` ``` Distinct_Values All_Values ---------------- ---------------- 159180469909.18 158762853821.10 ``` ### C. Using VAR with OVER The following example returns the statistical variance of the sales quota values for each quarter in a calendar year. Notice that the ORDER BY in the OVER clause orders the statistical variance and the ORDER BY of the SELECT statement orders the result set. ```sql -- Uses AdventureWorks SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota, VAR(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Variance FROM dbo.FactSalesQuota WHERE EmployeeKey = 272 AND CalendarYear = 2002 ORDER BY CalendarQuarter; ``` ``` Year Quarter SalesQuota Variance ---- ------- ---------------------- ------------------- 2002 1 91000.0000 null 2002 2 140000.0000 1200500000.00 2002 3 70000.0000 1290333333.33 2002 4 154000.0000 1580250000.00 ``` ## Related content VARP()Returns the statistical variance for the population for all values in the specified expression. Transact-SQL syntax conventions ## Syntax ```syntaxsql -- Aggregate Function Syntax VARP ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax VARP ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) ``` ## Arguments ALL Applies the function to all values. ALL is the default. DISTINCT Specifies that each unique value is considered. *expression* Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted. OVER ( [ _partition\_by\_clause_ ] _order\_by\_clause_) _partition\_by\_clause_ divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. _order\_by\_clause_ determines the logical order in which the operation is performed. _order\_by\_clause_ is required. For more information, see OVER Clause (Transact-SQL). ## Return Types float ## Remarks If VARP is used on all items in a SELECT statement, each value in the result set is included in the calculation. VARP can be used with numeric columns only. Null values are ignored. VARP is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. ## Examples ### A: Using VARP The following example returns the variance for the population for all bonus values in the `SalesPerson` table in the database. ```sql SELECT VARP(Bonus) FROM Sales.SalesPerson; GO ``` ## Examples: and ### B: Using VARP The following example returns the `VARP` of the sales quota values in the table `dbo.FactSalesQuota`. The first column contains the variance of all distinct values and the second column contains the variance of all values including any duplicates values. ```sql -- Uses AdventureWorks SELECT VARP(DISTINCT SalesAmountQuota)AS Distinct_Values, VARP(SalesAmountQuota) AS All_Values FROM dbo.FactSalesQuota; ``` ``` Distinct_Values All_Values ---------------- ---------------- 158146830494.18 157788848582.94 ``` ### C. Using VARP with OVER The following example returns the `VARP` of the sales quota values for each quarter in a calendar year. Notice that the ORDER BY in the OVER clause orders the statistical variance and the ORDER BY of the SELECT statement orders the result set. ```sql -- Uses AdventureWorks SELECT CalendarYear AS Year, CalendarQuarter AS Quarter, SalesAmountQuota AS SalesQuota, VARP(SalesAmountQuota) OVER (ORDER BY CalendarYear, CalendarQuarter) AS Variance FROM dbo.FactSalesQuota WHERE EmployeeKey = 272 AND CalendarYear = 2002 ORDER BY CalendarQuarter; ``` ``` Year Quarter SalesQuota Variance ---- ------- ---------------------- ------------------- 2002 1 91000.0000 0.00 2002 2 140000.0000 600250000.00 2002 3 70000.0000 860222222.22 2002 4 154000.0000 1185187500.00 ``` ## Related content

Date and Time Functions14

CAST and CONVERT()Reference for the CAST and CONVERT Transact-SQL functions. These functions convert expressions from one data type to another.DATEDIFF()Transact-SQL reference for the DATEDIFF function. Returns the numerical difference between a start and end date based on datepart.DATEFROMPARTS()This function returns a date value that maps to the specified year, month, and day values. Transact-SQL syntax conventions ## Syntax ```syntaxsql DATEFROMPARTS ( year, month, day ) ``` ## Arguments *year* An integer expression that specifies a year. *month* An integer expression that specifies a month, from 1 to 12. *day* An integer expression that specifies a day. ## Return types date ## Remarks `DATEFROMPARTS` returns a date value, with the date portion set to the specified year, month and day, and the time portion set to the default. For invalid arguments, `DATEFROMPARTS` will raise an error. `DATEFROMPARTS` returns null if at least one required argument has a null value. This function can handle remoting to servers and above. It cannot handle remoting to servers with a version below . ## Examples This example shows the `DATEFROMPARTS` function in action. ```sql SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result; ``` ``` Result ---------------------------------- 2010-12-31 (1 row(s) affected) ``` ## Related content DATENAME()This function returns a character string representing the specified *datepart* of the specified *date*.DATEPART()Transact-SQL reference for the DATEPART function. This function returns an integer corresponding to the datepart of a specified date.DATETIME2FROMPARTS()This function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument. Transact-SQL syntax conventions ## Syntax ```syntaxsql DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) ``` ## Arguments *year* An integer expression that specifies a year. *month* An integer expression that specifies a month. *day* An integer expression that specifies a day. *hour* An integer expression that specifies the hours. *minute* An integer expression that specifies the minutes. *seconds* An integer expression that specifies the seconds. *fractions* An integer expression that specifies a fractional seconds value. *precision* An integer expression that specifies the precision of the datetime2 value that `DATETIME2FROMPARTS` will return. ## Return types datetime2( *precision* ) ## Remarks `DATETIME2FROMPARTS` returns a fully initialized datetime2 value. `DATETIME2FROMPARTS` will raise an error if at least one required argument has an invalid value. `DATETIME2FROMPARTS` returns null if at least one required argument has a null value. However, if the *precision* argument has a null value, `DATETIME2FROMPARTS` will raise an error.DATETIMEFROMPARTS()DATETIMEFROMPARTS returns a datetime value for the specified date and time arguments.DATETIMEOFFSETFROMPARTS()Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument, and an offset as specified by the offset arguments. Transact-SQL syntax conventions ## Syntax ```syntaxsql DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision ) ``` ## Arguments DAY()This function returns an integer that represents the day (day of the month) of the specified *date*. See Date and Time Data Types and Functions (Transact-SQL) for an overview of all date and time data types and functions. Transact-SQL syntax conventions ## Syntax ```syntaxsql DAY ( date ) ``` ## Arguments *date* An expression that resolves to one of the following data types:FORMAT()The FORMAT function returns a value formatted with the specified format and optional culture.MONTH()Returns an integer that represents the month of the specified *date*. For an overview of all date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). Transact-SQL syntax conventions ## Syntax ```syntaxsql MONTH ( date ) ``` ## Arguments *date* Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The *date* argument can be an expression, column expression, user-defined variable, or string literal. ## Return Type int ## Return Value MONTH returns the same value as DATEPART (month, *date*). If *date* contains only a time part, the return value is 1, the base month. ## Examples The following statement returns `4`. This is the number of the month. ```sql SELECT MONTH('2007-04-30T01:01:01.1234567 -07:00'); ``` The following statement returns `1900, 1, 1`. The argument for *date* is the number `0`. interprets `0` as January 1, 1900. ```sql SELECT YEAR(0), MONTH(0), DAY(0); ``` ## Examples: and The following example returns `4`. This is the number of the month. ```sql -- Uses AdventureWorks SELECT TOP 1 MONTH('2007-04-30T01:01:01.1234') FROM dbo.DimCustomer; ``` The following example returns `1900, 1, 1`. The argument for *date* is the number `0`. interprets `0` as January 1, 1900. ```sql -- Uses AdventureWorks SELECT TOP 1 YEAR(0), MONTH(0), DAY(0) FROM dbo.DimCustomer; ``` ## Related content SMALLDATETIMEFROMPARTS()Returns a smalldatetime value for the specified date and time. Transact-SQL syntax conventions ## Syntax ```syntaxsql SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) ``` ## Arguments *year* Integer expression specifying a year. *month* Integer expression specifying a month. *day* Integer expression specifying a day. *hour* Integer expression specifying hours. *minute* Integer expression specifying minutes. ## Return Types smalldatetime ## Remarks This function acts like a constructor for a fully initialized smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned. This function is capable of being remoted to servers and above. It is not remoted to servers that have a version below . ## Examples ```sql SELECT SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) AS Result ``` ``` Result --------------------------- 2010-12-31 23:59:00 (1 row(s) affected) ```TIMEFROMPARTS()Returns a time value for the specified time and with the specified precision. Transact-SQL syntax conventions ## Syntax ```syntaxsql TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) ``` ## Arguments *hour* Integer expression specifying hours. *minute* Integer expression specifying minutes. *seconds* Integer expression specifying seconds. *fractions* Integer expression specifying fractions. *precision* Integer literal specifying the precision of the time value to be returned. ## Return Types time( *precision* ) ## Remarks TIMEFROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the *precision* argument is null, then an error is raised. The *fractions* argument depends on the *precision* argument. For example, if *precision* is 7, then each fraction represents 100 nanoseconds; if *precision* is 3, then each fraction represents a millisecond. If the value of *precision* is zero, then the value of *fractions* must also be zero; otherwise, an error is raised. This function can be remoted to servers and higher. It cannot be remoted to servers that have a version lower than . ## Examples ### A. Simple example without fractions of a second ```sql SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result; ``` ``` Result -------------------- 23:59:59.0000000 (1 row(s) affected) ``` ### B. Example with fractions of a second The following example demonstrates the use of the *fractions* and *precision* parameters: 1. When *fractions* has a value of 5 and *precision* has a value of 1, then the value of *fractions* represents 5/10 of a second. 2. When *fractions* has a value of 50 and *precision* has a value of 2, then the value of *fractions* represents 50/100 of a second. 3. When *fractions* has a value of 500 and *precision* has a value of 3, then the value of *fractions* represents 500/1000 of a second. ```sql SELECT TIMEFROMPARTS ( 14, 23, 44, 5, 1 ); SELECT TIMEFROMPARTS ( 14, 23, 44, 50, 2 ); SELECT TIMEFROMPARTS ( 14, 23, 44, 500, 3 ); GO ``` ``` ---------------- 14:23:44.5 (1 row(s) affected) ---------------- 14:23:44.50 (1 row(s) affected) ---------------- 14:23:44.500 (1 row(s) affected) ```YEAR()Returns an integer that represents the year of the specified *date*. For an overview of all date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). Transact-SQL syntax conventions ## Syntax ```syntaxsql YEAR ( date ) ``` ## Arguments *date* Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The *date* argument can be an expression, column expression, user-defined variable or string literal. ## Return Types int ## Return Value YEAR returns the same value as DATEPART (year, *date*). If *date* only contains a time part, the return value is 1900, the base year. ## Examples The following statement returns `2010`. This is the number of the year. ```sql SELECT YEAR('2010-04-30T01:01:01.1234567-07:00'); ``` The following statement returns `1900, 1, 1`. The argument for *date* is the number `0`. interprets `0` as January 1, 1900. ```sql SELECT YEAR(0), MONTH(0), DAY(0); ``` ## Examples: and The following statement returns `1900, 1, 1`. The argument for *date* is the number `0`. interprets `0` as January 1, 1900. ```sql SELECT TOP 1 YEAR(0), MONTH(0), DAY(0); ``` ## Related content

Metadata Functions11

DATABASEPROPERTYEX()DATABASEPROPERTYEX returns the current setting of the specified database option or property.DB_ID()This function returns the database identification (ID) number of a specified database. Transact-SQL syntax conventions ## Syntax ```syntaxsql DB_ID ( [ 'database_name' ] ) ``` ## Arguments '*database_name*' The name of the database whose database ID number `DB_ID` will return. If the call to `DB_ID` omits *database_name*, `DB_ID` returns the ID of the current database. ## Return types int DB_NAME()The DB_NAME function returns the name of a specified database.FILE_IDEX()This function returns the file identification (ID) number for the specified logical name of a data, log, or full-text file of the current database. Transact-SQL syntax conventions ## Syntax ```syntaxsql FILE_IDEX ( file_name ) ``` ## Arguments *file_name* An expression of type sysname that returns the file ID value 'FILE_IDEX' for the name of the file. ## Return Types int NULL on error ## Remarks *file_name* corresponds to the logical file name displayed in the name column from the sys.master_files or sys.database_files catalog views. Use `FILE_IDEX` in a SELECT list, a WHERE clause, or anywhere that supports use of an expression. For more information, see Expressions (Transact-SQL). ## Examples ### A. Retrieving the file id of a specified file This example returns the file ID for the `AdventureWorks_Data` file. ```sql USE AdventureWorks2022; GO SELECT FILE_IDEX('AdventureWorks2022_Data') AS 'File ID'; GO ``` ``` File ID ------- 1 (1 row(s) affected) ``` ### B. Retrieving the file id when the file name is not known This example returns the file ID of the `AdventureWorks` log file. The Transact-SQL (T-SQL) code snippet selects the logical file name from the `sys.database_files` catalog view, where the file type equals `1` (log). ```sql USE AdventureWorks2022; GO SELECT FILE_IDEX((SELECT TOP (1) name FROM sys.database_files WHERE type = 1)) AS 'File ID'; GO ``` ``` File ID ------- 2 ``` ### C. Retrieving the file id of a full-text catalog file This example returns the file ID of a full-text file. The T-SQL code snippet selects the logical file name from the `sys.database_files` catalog view, where the file type equals `4` (full-text). This code returns 'NULL' if a full-text catalog does not exist. ```sql SELECT FILE_IDEX((SELECT name FROM sys.master_files WHERE type = 4)) AS 'File_ID'; ``` ## Related content FULLTEXTSERVICEPROPERTY()Returns information related to the properties of the Full-Text Engine. These properties can be set and retrieved by using sp_fulltext_service. Transact-SQL syntax conventions ## Syntax ```syntaxsql FULLTEXTSERVICEPROPERTY ('property') ``` ## Arguments *property* Is an expression containing the name of the full-text service-level property. The table lists the properties and provides descriptions of the information returned. > [!NOTE] > The following properties will be removed in a future release of : ConnectTimeout, DataTimeout, and ResourceUsage. Avoid using these properties in new development work, and plan to modify applications that currently use any of them. |Property|Value| |--------------|-----------| |ResourceUsage|Returns 0. Supported for backward compatibility only.| |ConnectTimeout|Returns 0. Supported for backward compatibility only.| |IsFulltextInstalled|The full-text component is installed with the current instance of .<br /><br /> 0 = Full-text is not installed.<br /><br /> 1 = Full-text is installed.<br /><br /> NULL = Invalid input, or error.| |DataTimeout|Returns 0. Supported for backward compatibility only.| |LoadOSResources|Indicates whether operating system word breakers and filters are registered and used with this instance of . By default, this property is disabled to prevent inadvertent behavior changes by updates made to the operating system (OS). Enabling use of OS resources provides access to resources for languages and document types registered with Indexing Service, but that do not have an instance-specific resource installed. If you enable the loading of OS resources, ensure that the OS resources are trusted signed binaries; otherwise, they cannot be loaded when VerifySignature is set to 1.<br /><br /> 0 = Use only filters and word breakers specific to this instance of .<br /><br /> 1 = Load OS filters and word breakers.| |VerifySignature|Specifies whether only signed binaries are loaded by the Search Service. By default, only trusted, signed binaries are loaded.<br /><br /> 0 = Do not verify whether or not binaries are signed.<br /><br /> 1 = Verify that only trusted, signed binaries are loaded.| ## Return Types int ## Examples The following example checks whether only signed binaries are loaded, and the return value indicates that this verification is not occurring. ```sql SELECT fulltextserviceproperty('VerifySignature'); ``` ``` ----------- 0 ``` Note that to set signature verification back to its default value, 1, you can use the following `sp_fulltext_service` statement: ```sql EXEC sp_fulltext_service @action='verify_signature', @value=1; GO ``` ## Related content INDEXPROPERTY()Returns the named property value of a specified table identification number, index or statistics name, and property name.OBJECT_ID()OBJECT_ID returns the database object identification number of a schema-scoped object.OBJECT_SCHEMA_NAME()Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects (Transact-SQL).SCHEMA_ID()SCHEMA_ID returns the schema ID associated with a schema name.SCHEMA_NAME()Returns the schema name associated with a schema ID. Transact-SQL syntax conventions ## Syntax ```syntaxsql SCHEMA_NAME ( [ schema_id ] ) ``` ## Arguments |Term|Definition| |----------|----------------| |*schema_id*|The ID of the schema. *schema_id* is an int. If *schema_id* is not defined, SCHEMA_NAME will return the name of the default schema of the caller.| ## Return Types sysname Returns NULL when *schema_id* is not a valid ID. ## Remarks SCHEMA_NAME returns names of system schemas and user-defined schemas. SCHEMA_NAME can be called in a select list, in a WHERE clause, and anywhere an expression is allowed. ## Examples ### A. Returning the name of the default schema of the caller ```sql SELECT SCHEMA_NAME(); ``` ### B. Returning the name of a schema by using an ID ```sql SELECT SCHEMA_NAME(1); ``` ## Related content TYPEPROPERTY()Returns information about a data type. Transact-SQL syntax conventions ## Syntax ```syntaxsql TYPEPROPERTY (type , property) ``` ## Arguments *type* Is the name of the data type. *property* Is the type of information to be returned for the data type. *property* can be one of the following values. |Property|Description|Value returned| |--------------|-----------------|--------------------| |AllowsNull|Data type allows for null values.|1 = True<br /><br /> 0 = False<br /><br /> NULL = Data type not found.| |OwnerId|Owner of the type.<br /><br /> Note: The schema owner is not necessarily the type owner.|Nonnull = The database user ID of the type owner.<br /><br /> NULL = Unsupported type, or type ID is not valid.| |Precision|Precision for the data type.|The number of digits or characters.<br /><br /> -1 = xml or large value data type<br /><br /> NULL = Data type not found.| |Scale|Scale for the data type.|The number of decimal places for the data type.<br /><br /> NULL = Data type is not numeric or not found.| |UsesAnsiTrim|ANSI padding setting was ON when the data type was created.|1 = True<br /><br /> 0 = False<br /><br /> NULL = Data type not found, or it is not a binary or string data type.| ## Return Types int ## Exceptions Returns NULL on error or if a caller does not have permission to view the object. In , a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as TYPEPROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration. ## Examples ### A. Identifying the owner of a data type The following example returns the owner of a data type. ```sql SELECT TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId') AS owner_id, name, system_type_id, user_type_id, schema_id FROM sys.types; ``` ### B. Returning the precision of the tinyint data type The following example returns the precision or number of digits for the `tinyint` data type. ```sql SELECT TYPEPROPERTY( 'tinyint', 'PRECISION'); ``` ## Related content

Analytic Functions8

CUME_DIST()For , this function calculates the cumulative distribution of a value within a group of values. In other words, `CUME_DIST` calculates the relative position of a specified value in a group of values. Assuming ascending ordering, the `CUME_DIST` of a value in row _r_ is defined as the number of rows with values less than or equal to that value in row _r_, divided by the number of rows evaluated in the partition or query result set. `CUME_DIST` is similar to the `PERCENT_RANK` function. Transact-SQL syntax conventions ## Syntax ```syntaxsql CUME_DIST( ) OVER ( [ partition_by_clause ] order_by_clause ) ```FIRST_VALUE()Returns the first value in an ordered set of values.LAG()Accesses data from a previous row in the same result set without the use of a self-join starting with . LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row. Transact-SQL syntax conventions ## Syntax ```syntaxsql LAG (scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause ) ``` ## Arguments LAST_VALUE()Returns the last value in an ordered set of values.LEAD()LEAD accesses data from a subsequent row in the same result set without the use of a self-join.PERCENT_RANK()PERCENT_RANK calculates the relative rank of a row within a group of rows in the SQL Server Database Engine.PERCENTILE_CONT()PERCENTILE_CONT calculates a percentile based on a continuous distribution of the column value.PERCENTILE_DISC()PERCENTILE_DISC computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct partitions.

Security Functions8

HAS_PERMS_BY_NAME()Evaluates the effective permission of the current user on a securable. A related function is fn_my_permissions. Transact-SQL syntax conventions ## Syntax ```syntaxsql HAS_PERMS_BY_NAME ( securable , securable_class , permission [ , sub-securable ] [ , sub-securable_class ] ) ``` ## Arguments *securable* Is the name of the securable. If the securable is the server itself, this value should be set to NULL. *securable* is a scalar expression of type sysname. There is no default. *securable_class* Is the name of the class of securable against which the permission is tested. *securable_class* is a scalar expression of type nvarchar(60). In , the securable_class argument must be set to one of the following: DATABASE, OBJECT, ROLE, SCHEMA, or USER. *permission* A nonnull scalar expression of type sysname that represents the permission name to be checked. There is no default. The permission name ANY is a wildcard. *sub-securable* An optional scalar expression of type sysname that represents the name of the securable sub-entity against which the permission is tested. The default is NULL. > [!NOTE] > Sub-securables cannot use brackets in the form '[_sub name_]'. Use '_sub name_' instead. *sub-securable_class* An optional scalar expression of type nvarchar(60) that represent the class of securable subentity against which the permission is tested. The default is NULL. In , the sub-securable_class argument is valid only if the securable_class argument is set to OBJECT. If the securable_class argument is set to OBJECT, the sub-securable_class argument must be set to COLUMN. ## Return Types int Returns NULL when the query fails. ## Remarks This built-in function tests whether the current principal has a particular effective permission on a specified securable. HAS_PERMS_BY_NAME returns 1 when the user has effective permission on the securable, 0 when the user has no effective permission on the securable, and NULL when the securable class or permission is not valid. An effective permission is any of the following: - A permission granted directly to the principal, and not denied. - A permission implied by a higher-level permission held by the principal and not denied. - A permission granted to a role or group of which the principal is a member, and not denied. - A permission held by a role or group of which the principal is a member, and not denied. The permission evaluation is always performed in the security context of the caller. To determine whether some other user has an effective permission, the caller must have IMPERSONATE permission on that user. For schema-level entities, one-, two-, or three-part nonnull names are accepted. For database-level entities a one-part name is accepted, with a null value meaning "current database". For the server itself, a null value (meaning "current server") is required. This function cannot check permissions on a linked server or on a Windows user for which no server-level principal has been created. The following query will return a list of built-in securable classes: ``` SELECT class_desc FROM sys.fn_builtin_permissions(default); ``` The following collations are used: - Current database collation: Database-level securables that include securables not contained by a schema; one- or two-part schema-scoped securables; target database when using a three-part name. - master database collation: Server-level securables. - 'ANY' is not supported for column-level checks. You must specify the appropriate permission. ## Examples ### A. Do I have the server-level VIEW SERVER STATE permission? Applies to: and later ```sql SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE'); ``` ### B. Am I able to IMPERSONATE server principal Ps? Applies to: and later ```sql SELECT HAS_PERMS_BY_NAME('Ps', 'LOGIN', 'IMPERSONATE'); ``` ### C. Do I have any permissions in the current database? ```sql SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY'); ``` ### D. Does database principal Pd have any permission in the current database? Assume caller has IMPERSONATE permission on principal `Pd`. ```sql EXECUTE AS user = 'Pd' GO SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'ANY'); GO REVERT; GO ``` ### E. Can I create procedures and tables in schema S? The following example requires `ALTER` permission in `S` and `CREATE PROCEDURE` permission in the database, and similarly for tables. ```sql SELECT HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE PROCEDURE') & HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_procs, HAS_PERMS_BY_NAME(db_name(), 'DATABASE', 'CREATE TABLE') & HAS_PERMS_BY_NAME('S', 'SCHEMA', 'ALTER') AS _can_create_tables; ``` ### F. Which tables do I have SELECT permission on? ```sql SELECT HAS_PERMS_BY_NAME (QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'SELECT') AS have_select, * FROM sys.tables ``` ### G. Do I have INSERT permission on the SalesPerson table in AdventureWorks2022? The following example assumes is my current database context, and uses a two-part name. ```sql SELECT HAS_PERMS_BY_NAME('Sales.SalesPerson', 'OBJECT', 'INSERT'); ``` The following example makes no assumptions about my current database context, and uses a three-part name. ```sql SELECT HAS_PERMS_BY_NAME('AdventureWorks2022.Sales.SalesPerson', 'OBJECT', 'INSERT'); ``` ### H. Which columns of table T do I have SELECT permission on? ```sql SELECT name AS column_name, HAS_PERMS_BY_NAME('T', 'OBJECT', 'SELECT', name, 'COLUMN') AS can_select FROM sys.columns AS c WHERE c.object_id=object_id('T'); ``` ## Related content IS_SRVROLEMEMBER()Indicates whether a login is a member of the specified server role. Transact-SQL syntax conventions ## Syntax ```syntaxsql IS_SRVROLEMEMBER ( 'role' [ , 'login' ] ) ``` ## Arguments ' *role* ' Is the name of the server role that is being checked. *role* is sysname. Valid values for *role* are user-defined server roles, and the following fixed server roles:SESSION_USER()SESSION_USER returns the user name of the current context in the current database. Transact-SQL syntax conventions ## Syntax ```syntaxsql SESSION_USER ``` ## Return Types nvarchar(128) ## Remarks Use SESSION_USER with DEFAULT constraints in either the CREATE TABLE or ALTER TABLE statements, or use it as any standard function. SESSION_USER can be inserted into a table when no default value is specified. This function takes no arguments. SESSION_USER can be used in queries. If SESSION_USER is called after a context switch, SESSION_USER will return the user name of the impersonated context. ## Examples ### A. Using SESSION_USER to return the user name of the current session The following example declares a variable as `nchar`, assigns the current value of `SESSION_USER` to that variable, and then prints the variable with a text description. ```sql DECLARE @session_usr NCHAR(30); SET @session_usr = SESSION_USER; SELECT 'This session''s current user is: '+ @session_usr; GO ``` This is the result set when the session user is `Surya`: ``` -------------------------------------------------------------- This session's current user is: Surya SUSER_ID()Returns the login identification number of the user. > [!NOTE] > Starting with , SUSER_ID returns the value listed as principal_id in the sys.server_principals catalog view. Transact-SQL syntax conventions ## Syntax ```syntaxsql SUSER_ID ( [ 'login' ] ) ``` ## Arguments ' *login* ' Is the login name of the user. *login* is nchar. If *login* is specified as char, *login* is implicitly converted to nchar. *login* can be any login or Windows user or group that has permission to connect to an instance of . If *login* is not specified, the login identification number for the current user is returned. If the parameter contains the word NULL will return NULL. ## Return Types int ## Remarks SUSER_ID returns an identification number only for logins that have been explicitly provisioned inside . This ID is used within to track ownership and permissions. This ID is not equivalent to the SID of the login that is returned by SUSER_SID. If *login* is a SQL Server login, the SID maps to a GUID. If *login* is a Windows login or Windows group, the SID maps to a Windows security identifier. SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed, and must always be followed by parentheses, even if no parameter is specified. ## Examples The following example returns the login identification number for the `sa` login. ```sql SELECT SUSER_ID('sa'); ``` ## Related content SUSER_NAME()SUSER_NAME returns the login identification name of the user.SUSER_SID()SUSER_SID returns the security identification number (SID) for the specified login name.SYSTEM_USER()Allows a system-supplied value for the current login to be inserted into a table when no default value is specified. Transact-SQL syntax conventions ## Syntax ```syntaxsql SYSTEM_USER ```USER_NAME()USER_NAME returns a database user name from a specified identification number, or the current user name.

Ranking Functions4

DENSE_RANK()This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.NTILE()The NTILE ranking function distributes the rows in an ordered partition into a specified number of groups.RANK()RANK returns the rank of each row within the partition of a result set.ROW_NUMBER()Transact-SQL reference for the ROW_NUMBER function. This function numbers the output of a result set.

JSON Functions3

ISJSON()ISJSON tests whether a string contains valid JSON.JSON_ARRAY()JSON_ARRAY constructs JSON array text from zero or more expressions.JSON_OBJECT()JSON_OBJECT constructs JSON object text from zero or more expressions.

System Functions2

CONNECTIONPROPERTY()For a request that comes in to the server, this function returns information about the connection properties of the unique connection which supports that request. Transact-SQL syntax conventions ## Syntax ```syntaxsql CONNECTIONPROPERTY ( property ) ```FORMATMESSAGE()Constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing. Transact-SQL syntax conventions ## Syntax ```syntaxsql FORMATMESSAGE ( { msg_number | ' msg_string ' | @msg_variable} , [ param_value [ ,...n ] ] ) ``` ## Arguments *msg_number* Is the ID of the message stored in sys.messages. If *msg_number* is <= 13000, or if the message does not exist in sys.messages, NULL is returned. *msg_string* Applies to: ( through current version). Is a string enclosed in single quotes and containing parameter value placeholders. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For information about the structure of a message string and the use of parameters in the string, see the description of the *msg_str* argument in RAISERROR (Transact-SQL).

Cryptographic Functions2

KEY_GUID()Returns the GUID of a symmetric key in the database. Transact-SQL syntax conventions ## Syntax ```syntaxsql Key_GUID( 'Key_Name' ) ``` ## Arguments ' *Key_Name* ' The name of a symmetric key in the database. ## Return Types uniqueidentifier ## Remarks If an identity value was specified when the key was created, its GUID is an MD5 hash of that identity value. If no identity value was specified, the server generated the GUID. If the key is a temporary key, the key name must start with a number sign (#). ## Permissions Because temporary keys are only available in the session in which they are created, no permissions are required to access them. To access a key that is not temporary, the caller requires some permission on the key and must not have been denied VIEW permission on the key. ## Examples The following example returns the GUID of a symmetric key called `ABerglundKey1`. ```sql SELECT Key_GUID('ABerglundKey1'); ``` ## Related content KEY_ID()Returns the ID of a symmetric key in the current database. Transact-SQL syntax conventions ## Syntax ```syntaxsql Key_ID ( 'Key_Name' ) ``` ## Arguments ' *Key_Name* ' The name of a symmetric key in the database. ## Return Types int ## Remarks The name of a temporary key must start with a number sign (#). ## Permissions Because temporary keys are only available in the session in which they are created, no permissions are required to access them. To access a key that is not temporary, the caller needs some permission on the key and must not have been denied VIEW permission on the key. ## Examples ### A. Returning the ID of a symmetric key The following example returns the ID of a key called `ABerglundKey1`. ```sql SELECT KEY_ID('ABerglundKey1'); ``` ### B. Returning the ID of a temporary symmetric key The following example returns the ID of a temporary symmetric key. Note that `#` is prepended to the key name. ```sql SELECT KEY_ID('#ABerglundKey2'); ``` ## Related content

Conversion Functions2

PARSE()Returns the result of an expression, translated to the requested data type in . Transact-SQL syntax conventions ## Syntax ```syntaxsql PARSE ( string_value AS data_type [ USING culture ] ) ``` ## Arguments *string_value* nvarchar(4000) value representing the formatted value to parse into the specified data type. *string_value* must be a valid representation of the requested data type, or PARSE raises an error. *data_type* Literal value representing the data type requested for the result. *culture* Optional string that identifies the culture in which *string_value* is formatted. If the *culture* argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. *culture* accepts any culture supported by the .NET Framework; it is not limited to the languages explicitly supported by . If the *culture* argument is not valid, PARSE raises an error. ## Return Types Returns the result of the expression, translated to the requested data type. ## Remarks Null values passed as arguments to PARSE are treated in two ways: 1. If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner. 2. If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch. Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. PARSE relies on the presence of the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server. More information about the data_type parameter The values for the *data_type* parameter are restricted to the types shown in the following table, together with styles. The style information is provided to help determine what types of patterns are allowed. For more information on styles, see the .NET Framework documentation for the System.Globalization.NumberStyles and DateTimeStyles enumerations. |Category|Type|.NET Framework type|Styles used| |--------------|----------|-------------------------|-----------------| |Numeric|bigint|Int64|NumberStyles.Number| |Numeric|int|Int32|NumberStyles.Number| |Numeric|smallint|Int16|NumberStyles.Number| |Numeric|tinyint|Byte|NumberStyles.Number| |Numeric|decimal|Decimal|NumberStyles.Number| |Numeric|numeric|Decimal|NumberStyles.Number| |Numeric|float|Double|NumberStyles.Float| |Numeric|real|Single|NumberStyles.Float| |Numeric|smallmoney|Decimal|NumberStyles.Currency| |Numeric|money|Decimal|NumberStyles.Currency| |Date and Time|date|DateTime|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| |Date and Time|time|TimeSpan|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| |Date and Time|datetime|DateTime|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| |Date and Time|smalldatetime|DateTime|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| |Date and Time|datetime2|DateTime|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| |Date and Time|datetimeoffset|DateTimeOffset|DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal| More information about the culture parameter The following table shows the mappings from languages to .NET Framework cultures. |Full name|Alias|LCID|Specific culture| |---------------|-----------|----------|----------------------| |us_english|English|1033|en-US| |Deutsch|German|1031|de-DE| |Français|French|1036|fr-FR| |日本語|Japanese|1041|ja-JP| |Dansk|Danish|1030|da-DK| |Español|Spanish|3082|es-ES| |Italiano|Italian|1040|it-IT| |Nederlands|Dutch|1043|nl-NL| |Norsk|Norwegian|2068|nn-NO| |Português|Portuguese|2070|pt-PT| |Suomi|Finnish|1035|fi-FI| |Svenska|Swedish|1053|sv-SE| |čeština|Czech|1029|Cs-CZ| |magyar|Hungarian|1038|Hu-HU| |polski|Polish|1045|Pl-PL| |română|Romanian|1048|Ro-RO| |hrvatski|Croatian|1050|hr-HR| |slovenčina|Slovak|1051|Sk-SK| |slovenski|Slovenian|1060|Sl-SI| |ελληνικά|Greek|1032|El-GR| |български|Bulgarian|1026|bg-BG| |русский|Russian|1049|Ru-RU| |Türkçe|Turkish|1055|Tr-TR| |British|British English|2057|en-GB| |eesti|Estonian|1061|Et-EE| |latviešu|Latvian|1062|lv-LV| |lietuvių|Lithuanian|1063|lt-LT| |Português (Brasil)|Brazilian|1046|pt-BR| |繁體中文|Traditional Chinese|1028|zh-TW| |한국어|Korean|1042|Ko-KR| |简体中文|Simplified Chinese|2052|zh-CN| |Arabic|Arabic|1025|ar-SA| |ไทย|Thai|1054|Th-TH| ## Examples ### A. PARSE into datetime2 ```sql SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result; ``` ``` Result --------------- 2010-12-13 00:00:00.0000000 (1 row(s) affected) ``` ### B. PARSE with currency symbol ```sql SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result; ``` ``` Result --------------- 345.98 (1 row(s) affected) ``` ### C. PARSE with implicit setting of language ```sql -- The English language is mapped to en-US specific culture SET LANGUAGE 'English'; SELECT PARSE('12/16/2010' AS datetime2) AS Result; ``` ``` Result --------------- 2010-12-16 00:00:00.0000000 (1 row(s) affected) ```TRY_PARSE()TRY_PARSE returns the result of an expression, translated to the requested data type, or NULL if the cast fails.