Home • About • Contact
Login • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

• Intro
• User-Defined
• Aggregate
• Configuration
• Cursor
• Date and Time
• Mathematical
• Meta Data
• Rowset
• Security
• String
• System
• System Statistical
• Text and Image

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)

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

Page Modified: (Hand noted: 2007-10-12 21:45:09Z) (Auto noted: 2008-06-04 14:55:04Z)