Intro

There are two kinds of functions supported in SQL Server:

  • User-defined .
  • Built-in. This section categorizes, lists and briefly discusses the functions built-in to SQL Server. Functions in bold are my favorites.

Functions are also either deterministic or non-deterministic. A deterministic function always returns the same results when fed the same arguments. A non-deterministic function may return different results for the same set of arguments.

This section was done for SQL Server 2000. I haven't stuck in stuff for SQL Server 2005 yet.

User-Defined

A user-defined function take parameters and returns a single value. There are three kinds of user-defined functions:

  • Scalar Functions . Functions that return a scalar value.
  • Functions that return a table value.
    • Inline Table-Valued Functions. The RETURN keyword of the CREATE FUNCTION statement does not specify a column list, and just uses a SELECT statement.
    • Multi-Statement Table-Valued Functions. The RETURN keyword of the CREATE FUNCTION statement does specify a column list.

Here is the syntax for creating user-defined functions:

CREATE FUNCTION [OwnerName.]FunctionName     ([{@ParameterName [AS] ScalarParameterDataType[ = Default]}[, ...]])
-- Scalar Functions:
RETURNS ScalarReturnDataType
[WITH {ENCRYPTION | SCHEMABINDING}[[,] ...]]
[AS]
BEGIN
    FunctionBody
    RETURN ScalarExpression
END
-- Inline Table-Valued Functions:
RETURNS TABLE
[WITH {ENCRYPTION | SCHEMABINDING}[[,] ...]]
[AS]
RETURN [(]
    SELECTStatement
[)]
-- Multi-Statement Table-Valued Functions:
RETURNS @ReturnTableVariable TABLE TableDefinition
[WITH {EnCRYPTION | SCHEMABINDING}[[,] ...]]
[AS]
BEGIN
    FunctionBody
    RETURN
END

Here are some general notes on user-defined functions:

  • User-defined functions are created with the CREATE FUNCTION statement, modified ALTER FUNCTION, and deleted with DROP FUNCTION.
  • User-defined functions cannot have any side effects, i.e. they can only change objects local to the function. Put in another way, they cannot modify the global database state.
  • User-defined functions can be executed through an EXECUTE statement just like a store procedure.
  • Parameters:
    • Zero to 1,024 parameters can be specified.
    • To use the default value of a parameter, the keyword 'default' must be explicitly specified. This is different from stored procedures where the default value is used simply by not providing it.
    • Parameters are scalar data types, except for timestamp and user-defined data types. Non-scalar data types such as cursor and table cannot be used.
  • ScalarReturnDataType are scalar data types except for text, ntext, image, cursor, timestamp , and user-defined data types.
  • These built-in functions are not allowed in user-defined functions:
    @@CONNECTIONS @@PACK_SENT GETDATE
    @@CPU_BUSY @@PACKET_ERRORS GetUTCDate
    @@IDLE @@TIMETICKS NEWID
    @@IO_BUSY @@TOTAL_ERRORS RAND
    @@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
    @@PACK_RECEIVED @@TOTAL_WRITE  
  • Only these statements are allowed in the body of a function:
    • Assignment statements.
    • Control-of-Flow statements.
    • DECLARE statements defining data variables and cursors that are local to the function.
    • SELECT statements containing select lists with expressions that assign values to variables that are local to the function.
    • Cursor operations referencing local cursors that are declared, opened, closed, and de-allocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
    • INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
    • EXECUTE statements calling an extended stored procedures.
  • Using functions in SQL Statements:
    • Table valued functions can function as a table or a view in SQL statements.
    • A function can be referenced in the FROM clause whereas a stored procedure cannot!
    • Calls to scalar user-defined functions need at least a two-part name (eg SELECT *, UserX.AScalarFunc() FROM tbl1 ).
    • Calls to table valued user-defined functions need a one-part name (eg SELECT * FROM ATableFunc() ).
    • Calls to table valued built-in functions must be prefixed with two colons (eg SELECT * FROM ::fn_helpcollations() ).

EG Scalar User-Defined Function

This cubes a number:

CREATE FUNCTION FCubeMe(@SideOfCube float)
RETURNS float
AS
BEGIN
    RETURN (@SideOfCube * @SideOfCube * @SideOfCube)
END

EG Inline Table-Valued User-Defined Function

This makes a simple table:

CREATE FUNCTION FSomeTable(@State char(2))
RETURNS TABLE
AS
RETURN(SELECT * FROM tbl1 WHERE state = @State)

EG Split a CSV string

This takes a string that is a CSV (or otherwise delimited string) and returns a table of those values. It assumes that you have a table called com.SequenceNumbers with a column called SeqNo, which has values 1, 2, 3, ..., 1000000 or some other large number equivalent to the largest number of values you might receive.

CREATE FUNCTION [com].[Split] (
    @Array VARCHAR(MAX),
    @Delimiter CHAR(1) = ','
)
RETURNS TABLE
AS

RETURN (
    SELECT
        SeqNo - LEN(REPLACE(LEFT(@Array, SeqNo), @Delimiter, '')) + 1 AS Position,
        REPLACE(LTRIM(RTRIM(SUBSTRING(@Array, SeqNo, CHARINDEX(@Delimiter, @Array + @Delimiter, SeqNo) - SeqNo))), char(10),'') AS Element
    FROM com.SequenceNumbers
    WHERE
        SeqNo <= LEN(@Array) AND
        SUBSTRING(@Delimiter + @Array, SeqNo, 1) = @Delimiter
)

EG:

INSERT INTO MemberDog (MemberID, DogID)
SELECT 37, CAST(Element AS INT) FROM Split('7,8,43');

Aggregate

Aggregate functions return a single value based on applicable rows. Aggregate functions cannot be combined with simple column names in a SelectList, that return multiple values. Except for COUNT(*), all the aggregate functions ignore nulls.

These functions are all deterministic.

AVG MAX
BINARY_CHECKSUM MIN
CHECKSUM SUM
CHECKSUM_AGG STDEV
COUNT STDEVP
COUNT_BIG VAR
GROUPING VARP

Here is syntax (where expression is almost always a column name) for some of the more popular ones:

SUM ([ALL | DISTINCT] expression)
AVG ([ALL | DISTINCT] expression)
COUNT ([ALL | DISTINCT] expression)
COUNT(*) --Number of rows, even nulls
MAX(expression)
MIN(expression)

Examples:

SELECT SUM(price), COUNT(*), AVG(price), MAX(price), MIN(price)
FROM tbl1 WHERE price > 5
-- If tbl1 has prices of 1 thru 10,
-- then it returns this:
-- sum of 40, count of 5, avg of 8, max of 10, min of 5.

Configuration

These return info about current configuration option settings.

These functions are all non-deterministic.

@@DATEFIRST @@OPTIONS
@@DBTS @@REMSERVER
@@LANGID @@SERVERNAME
@@LANGUAGE @@SERVICENAME
@@LOCK_TIMEOUT @@SPID
@@MAX_CONNECTIONS @@TEXTSIZE
@@MAX_PRECISION @@VERSION
@@NESTLEVEL  

Cursor

These return info about cursors.

These functions are all non-deterministic.

@@CURSOR_ROWS
CURSOR_STATUS
@@FETCH_STATUS

Date and Time

Date and time related thingies in SQL Server. The functions are all deterministic except for DATENAME, GETDATE, GETUTCDATE.

  • Functions
    • DATEADD(DatePart, NumberToAdd, Date). Returns a new datetime value based on adding an interval to the specified date.
    • DATEDIFF(DatePart, StartDate, EndDate). Returns the number of date and time boundaries crossed between two specified dates.
    • DATENAME(DatePart, Date). Returns a character string representing the specified datepart of the specified date.
    • DATEPART(DatePart, Date). Returns an integer representing the specified datepart of the specified date.
    • DAY(). Returns an integer representing the day datepart of the specified date.
    • GETDATE(). Returns the current system date and time in the standard internal format for datetime values.
    • GETUTCDATE(). Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time).
    • MONTH(Date). Returns an integer that represents the month part of a specified date.
    • YEAR(Date). Returns an integer that represents the year part of a specified date.
  • Miscellany
    • @@DATEFIRST. Use to check which date is the first day of the week.
    • SET DATEFIRST {1|2|3|4|5|6|7|@NumberVariable}. English defaults to 7 (Sunday). Otherwise use 1 for Monday, etc.
    • SET DATEFORMAT{mdy|myd|ymd|ydm|dmy|dym|@FormatVariable}. Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data. English defaults to mdy (month/date/year).

Here are the values for DatePart accepted by SQL Server. Note that weekday is not usually used in DATEADD() or DATEDIFF().

DatePart Abbreviations Values
year yy, yyyy 1900-9999
quarter qq, q 1-3
month mm, m 1-3
dayofyear dy, y 1-366
day dd, d 1-31
weekday dw 1-7 (Mon.-Sun.)
week wk, ww 1-53
hour hh 0-23
minute mi, n 0-59
second ss, s 0-59
millisecond ms 0-999

Date Time Details

  • A search using = dateData will return only the dates whose time portion is also the default of 12:00:00:000AM.
  • A search using LIKE has SQL Server invisibly converts the date-time data into the varchar datatype.
    • Be sure to include wildcards since the resulting varchar will have both date and time info.
    • TSQL will insert a space before single digit days. EG: May  2 is returned, not May 2.
  • Date and time literals must be enclosed in quotations. EG: SELECT "Month Name" = DATENAME(month, 'February 28, 1969').
  • For signifying milliseconds, either a colon or a period is used.
    • A colon means the number indicates thousandths of seconds. EGs: These all have 3 milliseconds:
      11:30:20:3
      11:30:20:03
      11:30:20:003
    • A period means that the number indicates decimal portions of a second. EG:
      11:30:20.3   -- 3 tenths
      11:30:20.03  -- 3 hundredths
      11:30:20.003 -- 3 thousandths
      

Mathematical

Except for RAND, these functions are all non-deterministic.

ABS DEGREES RAND
ACOS EXP ROUND
ASIN FLOOR SIGN
ATAN LOG SIN
ATN2 LOG10 SQUARE
CEILING PI SQRT
COS POWER TAN
COT RADIANS  

Meta Data

These return info about the database and database objects.

These functions are all non-deterministic.

COL_LENGTH fn_listextendedproperty
COL_NAME FULLTEXTCATALOGPROPERTY
COLUMNPROPERTY FULLTEXTSERVICEPROPERTY
DATABASEPROPERTY INDEX_COL
DATABASEPROPERTYEX INDEXKEY_PROPERTY
DB_ID INDEXPROPERTY
DB_NAME OBJECT_ID
FILE_ID OBJECT_NAME
FILE_NAME OBJECTPROPERTY
FILEGROUP_ID @@PROCID
FILEGROUP_NAME SQL_VARIANT_PROPERTY
FILEGROUPPROPERTY TYPEPROPERTY
FILEPROPERTY  

Rowset

These return objects that can be used in place of a table. These are great!

These functions are all non-deterministic.

CONTAINSTABLE
FREETEXTTABLE
OPENDATASOURCE
OPENQUERY
OPENROWSET
OPENXML

Security

These return info about users and roles.

These functions are all non-deterministic.

fn_trace_geteventinfo IS_SRVROLEMEMBER
fn_trace_getfilterinfo SUSER_SID
fn_trace_getinfo SUSER_SNAME
fn_trace_gettable USER_ID
HAS_DBACCESS USER
IS_MEMBER  

String

These take a string and return a string or numeric result. These are great! Except for CHARINDEX and PATINDEX, these functions are all non-deterministic.

String related thingies in SQL Server.

  • Functions
    • ASCII(str). Returns the ASCII code value of the leftmost character of a character expression.
    • CHAR(int). A string function that converts an int ASCII code to a character.
    • CHARINDEX(strToFind, strSearchIn[, intStartLocation]). Returns the starting position of the specified expression in a character string.
    • DIFFERENCE(str1, str2). Returns the difference between the SOUNDEX values of two character expressions as an integer.
    • LEFT(str, int). Returns the part of a character string starting at a specified number of characters from the left.
    • LEN(str). Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.
    • LOWER(str). Returns a character expression after converting uppercase character data to lowercase.
    • LTRIM(str). Returns a character expression after removing leading blanks.
    • NCHAR(int). Returns the Unicode character with the given integer code, as defined by the Unicode standard.
    • PATINDEX( '%pattern%' , str). Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
    • REPLACE(strToFindIn, strFind, strReplaceWith). Replaces all occurrences of the second given string expression in the first string expression with a third expression.
    • QUOTENAME(sysnameString[, QuoteCharacter]). Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. If QuoteCharacter is not specified then square brackets are assumed.
    • REPLICATE(str, int). Repeats a character expression for a specified number of times.
    • REVERSE(str). Returns the reverse of a character expression.
    • RIGHT(str, int). Returns the part of a character string starting a specified number of integer_expression characters from the right.
    • RTRIM(str). Returns a character string after truncating all trailing blanks.
    • SOUNDEX(str). Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
    • SPACE(int). Returns a string of repeated spaces.
    • STR(Float[, intDesiredStringLength[, intDesiredDecimalPlacesToShow]]). Returns character data converted from numeric data. intDesiredStringLength defaults to 10.
    • STUFF(strTarget, intStart, intEnd, strToInsert). Deletes a specified length of characters and inserts another set of characters at a specified starting point.
    • SUBSTRING(expression, intStart, intEnd). Returns part of a character, binary, text, or image expression.
    • UNICODE(nstr). Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
    • UPPER(str). Returns a character expression with lowercase character data converted to uppercase.
  • Miscellany
    • @@TEXTSIZE. Returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns. The default size is 4096 bytes.
    • SET TEXTSIZE int. Specifies the size of text and ntext data returned with a SELECT statement.

System

These do things and get info about SQL Server.

Function Determinism
APP_NAME Nondeterministic
CASE expression Deterministic
CAST and CONVERT Deterministic unless used with datetime, smalldatetime, or sql_variant.
COALESCE Deterministic
COLLATIONPROPERTY Nondeterministic
CURRENT_TIMESTAMP Nondeterministic
CURRENT_USER Nondeterministic
DATALENGTH Deterministic
@@ERROR Nondeterministic
fn_helpcollations Deterministic
fn_servershareddrives Nondeterministic
fn_virtualfilestats Nondeterministic
FORMATMESSAGE Nondeterministic
GETANSINULL Nondeterministic
HOST_ID Nondeterministic
HOST_NAME Nondeterministic
IDENT_CURRENT Nondeterministic
IDENT_INCR Nondeterministic
IDENT_SEED Nondeterministic
@@IDENTITY Nondeterministic
IDENTITY (Function) Nondeterministic
ISDATE Deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and the style parameter is not equal to 0, 100, 9, or 109. Styles 0 and 100 use the default format mon dd yyyy hh:miAM (or PM). Styles 9 and 109 use the default format plus milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM).
ISNULL Deterministic
ISNUMERIC Deterministic
NEWID Nondeterministic
NULLIF Deterministic
PARSENAME Deterministic
PERMISSIONS Nondeterministic
@@ROWCOUNT Nondeterministic
ROWCOUNT_BIG Nondeterministic
SCOPE_IDENTITY Nondeterministic
SERVERPROPERTY Nondeterministic
SESSIONPROPERTY Nondeterministic
SESSION_USER Nondeterministic
STATS_DATE Nondeterministic
SYSTEM_USER Nondeterministic
@@TRANCOUNT Nondeterministic
USER_NAME Nondeterministic

CAST and CONVERT Syntax:

CONVERT(DataType[(length)], expression [, style])
-- A SQL Server function.
CAST(Expression AS DataType)
-- Preferred since this works for both SQL-92 & SQL Server.

System Statistical

These return statistical info about the system.

These functions are all non-deterministic.

@@CONNECTIONS @@PACK_RECEIVED
@@CPU_BUSY @@PACK_SENT
fn_virtualfilestats @@TIMETICKS
@@IDLE @@TOTAL_ERRORS
@@IO_BUSY @@TOTAL_READ
@@PACKET_ERRORS @@TOTAL_WRITE

Text and Image

These operate on text or image values and return statistical info about the value.

These functions are all non-deterministic.

PATINDEX
TEXTPTR
TEXTVALID


GeorgeHernandez.comSome rights reserved