SQL Server Functions

SQL Server starting with 2012 ship with a robust set of functions that can be used to make code perform faster, with fewer lines of code. The functions can be used in ETL Process to provide better error handling. A example of this would be the Try_Parse function that allows you to check if a value can be converted.

Another example would be using the FIRST_VALUE() and LAST_VALUE() functions which work against a set of data. These functions are very useful when looking for things like month over month averages and when doing calculations. The table below contain a list of function that are supported starting with 2012 along with some examples of how to use them.

Function Type Description Syntax
IIF() Logical Returns a value based on if a Boolean expression evaluate to true or false IIF( Boolean_expression, true_value, false_value)
CHOOSE() Logical Return value at the specified index from a list CHOOSE(Index, val_1, [,val_n])
FORMAT() String Returns a formatted string FORMAT(string, format, [culture])
CONCAT() String Concatenate multiple columns into one string. CONCAT( Column1, column2, Column3)
CAST() Conversion Cast data as a different data type CAST(value as <data_type> [Length])
CONVERT() Conversion Convert data to a different data type CONVERT( <data type> [Length], value [, style] )
PARSE() Conversion Returns an expression translated to a requested data type PARSE(string as data_type [ using culture])
TRY_CAST() Conversion Check to see if a value can be successfully cast as a data type TRY_CAST ( value AS data type [length])
TRY_CONVERT() Conversion Check to see if a value can be converted to a specified data type TRY_CONVERT( <data Type> [Length], value [, style] )
TRY_PARSE() Conversion Return null if Parse fails, otherwise return translated data type that’s requested. TRY_PARSE (value AS data type [ USING culture ] )
FIRST_VALUE() Analytical Return the first value in a ordered set FIRST_VAUE([scalar_expression] )OVER ([partition_by_clause] order_by_clause [rows_range_clause]))
LAST_VALUE() Analytical Return the last value of an ordered set FIRST_VAUE([scalar_expression] )OVER ([partition_by_clause] order_by_clause [rows_range_clause]))
LAG() Analytical Return data from the row before the current row in result frame LAG (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

LEAD() Analytical Return data from the row after the current row in result frame LEAD (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

RANK() Ranking Ranks each row in a partitioned result set RANK() OVER (<Partition by clause> ORDER BY )
DENSE_RANK() Ranking Returns rank without gaps in the partitioned result set. DENSERANK() OVER (<Partition by clause> ORDER BY )
NTILE() Ranking Divide the result set into groups NTILE() OVER (<Partition by clause> ORDER BY )
ROW_NUMBER() Ranking Returns the row number for each row in a result set. ROW_NUMBER () OVER (<Partition by clause> ORDER BY )
Logical Functions

String Functions

Conversion Functions

Analytical Functions

The first value function work against a ordered set in the example below the set we are working with is a table that has columns lastname, jobtitle and vacationhours. We use the first value function to get the name of the employee with the fewest number of vacation days. This is don by getting the first empolyee of a based on the partition by and order which in this case is jobtitle and vacationhours.

These are some examples of using functions in some simple use cases.

Leave a Reply