Nov 11

SQL Functions

User Defined Functions/SQL Functions

A user defined function or SQL Function is a reusable routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a single value or a table data.

There are two type of user defined SQL functions i.e. Scalar Functions and Table Value Functions

Scalar Functions – These type of function used for complex logic processing or complex calculations and returns the scalar (single) value of any type i.e. string, integer etc.

CREATE FUNCTION [dbo].[fnGetEmployeeCount]()  
RETURNS INT   
AS   
BEGIN  
    DECLARE @EmployeeCount INT;  
    SET @EmployeeCount = 0; 

    SELECT @EmployeeCount = COUNT(EmployeeId)   
    FROM Employee 

    RETURN @EmployeeCount;  
END;

To select employee count from database

SELECT dbo.fnGetEmployeeCount() AS EmployeeCount

Table Value Functions – These type of functions generally used for complex query processing and returns the data in tabular form. For example –  Getting employees for a department in tabular form.

CREATE FUNCTION [dbo].[fnGetEmployeeForDepartment](@DepartmentId int)  
RETURNS @ReturnEmployeeInformation TABLE 
(
    EmpID int NOT NULL, 
    EmployeeName varchar(50) NULL, 
    Department varchar(50) NULL
)
AS   
BEGIN  
        DECLARE @EmpID INT
        DECLARE @EmployeeName VARCHAR(50) 
        DECLARE @Department VARCHAR(50) 

	INSERT INTO @ReturnEmployeeInformation(EmpID, EmployeeName, Department)
	SELECT e.EmployeeId, E.EmployeeName, d.Name
	FROM Employee e INNER JOIN Department d ON e.DepartmentId = d.Id
	WHERE DepartmentId = @DepartmentId
	
	RETURN;
END

To select employees from testing department

SELECT * FROM dbo.fnGetEmployeeForDepartment(3)
System Provided Functions

SQL Server has many built-in defined functions. These functions are generally used to cast, converting values, dates, aggregating values in group by clause.

In this post I will include most frequently used built-in functions in SQL Server which are categorized into Aggregate, Date and Time, Mathematical, String, Rowset and Other functions.

  • Aggregate Functions

    Aggregate functions  frequently used with the GROUP BY clause  perform a calculation on a set of values and return a single value.

Function  Meaning
AVG Returns the average of the values in a group. Null values are ignored.
COUNT Returns the number of items in a group. COUNT always returns an int data type value.
MAX Returns the maximum value in the expression.
MIN Returns the minimum value in the expression.
SUM Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
  • Date and Time Functions

    All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running

Function  Meaning
CURRENT_TIMESTAMP Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. This function is the ANSI SQL equivalent to GETDATE.
GETDATE Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
DATEADD Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.
DATEDIFF Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate
DATEPART Returns an integer that represents the specified datepart of the specified date.
DAY Returns an integer representing the day (day of the month) of the specified date.
MONTH Returns an integer that represents the month of the specified date.
YEAR Returns an integer that represents the year of the specified date.
GETUTCDATE Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running
ISDATE Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
  • Mathematical Functions

    These are scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.

Function  Meaning
ABS A mathematical function that returns the absolute (positive) value of the specified numeric expression.
CEILING Returns the smallest integer greater than, or equal to, the specified numeric expression.
FLOOR Returns the largest integer less than or equal to the specified numeric expression.
RAND Returns a pseudo-random float value from 0 through 1, exclusive.
ROUND Returns a numeric value, rounded to the specified length or precision
  • String Functions

    These are scalar functions perform an operation on a string input value and return a string or numeric value. When string functions are passed arguments that are not string values, the input type is implicitly converted to a text data type

Function  Meaning
CHAR Converts an int ASCII code to a character.
CHARINDEX Searches an expression for another expression and returns its starting position if found.
LEFT Returns the left part of a character string with the specified number of characters.
RIGHT Returns the right part of a character string with the specified number of characters.
LEN Returns the number of characters of the specified string expression, excluding trailing blanks.
REPLACE Replaces all occurrences of a specified string value with another string value.
CONCAT Returns a string that is the result of concatenating two or more string values.
REVERSE Returns the reverse order of a string value.
SUBSTRING Returns part of a character, binary, text, or image expression in SQL Server.
STUFF The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
TRIM Removes the space character char(32) or other specified characters from the start or end of a string.
LTRIM Returns a character expression after it removes leading blanks.
RTRIM Returns a character string after truncating all trailing spaces.
LOWER Returns a character expression after converting uppercase character data to lowercase.
UPPER Returns a character expression with lowercase character data converted to uppercase.
  • Other Functions
Function  Meaning
CAST Converts an expression of one data type to another
COALESCE Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL
CONVERT Converts an expression of one data type to another
@@IDENTITY Is a system function that returns the last-inserted identity value.
ISNULL Replaces NULL with the specified replacement value.
ISNUMERIC Determines whether an expression is a valid numeric type.
NEWID Creates a unique value of type uniqueidentifier.
NULLIF Returns a null value if the two specified expressions are equal
ROW_NUMBER Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
@@ROWCOUNT Returns the number of rows affected by the last statement.
SCOPE_IDENTITY Returns the last identity value inserted into an identity column in the same scope

Microsoft has introduced new built-in functions like Security(encryption and decryption),  JSON, Cryptographic functions in SQL Server 2012 onward but may be part of professional and enterprise edition.  These functions generally used by senior developers and database expert. I use express edition and hence I will cover these part in my upcoming post.

Leave a Reply