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 frequently used with the GROUP BY clause perform a calculation on a set of values and return a single value.
|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
|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.|
These are scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value.
|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|
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
|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
|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.|
|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.