SQL Server System Functions

SQL Server System Functions

SQL Server system functions perform operations on and return information about values, objects, and settings in SQL Server.

Some system functions are deterministic while other system functions are nondeterministic.  System functions that are deterministic are CASE, CAST and CONVERT (unless used with datetime, smalldatetime, or sql_variant), COALESCE, DATALENGTH, fn_helpcollations, ISNULL, ISNUMERIC, NULLIF and PARSENAME.  All other system functions listed below are nondeterministic.

Function	Description
APP_NAME	Returns the application name for the current session if set by the application. Syntax: APP_NAME()
CASE	Evaluates a list of conditions and returns one of multiple possible result expressions.  CASE has two formats, both of which support an optional ELSE argument: The simple CASE function compares an expression to a set of simple expressions to determine the result. The searched CASE function evaluates a set of Boolean expressions to determine the result. Syntax: Simple CASE function: CASE <input_expression> WHEN <when_expression> THEN <result_expression> [ ... n ] [ ELSE <else_result_expression> ] END Searched CASE function: CASE WHEN <boolean_expression> THEN <result_expression> [ ... n ] [ ELSE <else_result_expression> ] END
CAST and CONVERT	Converts an expression of one data type to another. Syntax: Syntax for CAST: CAST ( <expression> AS <data_type> [ ( <length> ) ] ) Syntax for CONVERT: CONVERT ( <data_type> [ ( <length> ) ], <expression> [ , <style> ] )
COALESCE	Returns the first nonnull expression among its arguments. Syntax: COALESCE ( <expression> [, ... n ] )
COLLATIONPROPERTY	Returns the property of a specified collation. Syntax: COLLATIONPROPERTY ( '<collation_name>', '<property_name>' )
COLUMNS_UPDATED	Returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated.  COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions. Syntax: COLUMNS_UPDATE()
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. Syntax: CURRENT_TIMESTAMP
CURRENT_USER	Returns the name of the current user.  This function is equivalent to USER_NAME(). Syntax: CURRENT_USER
DATALENGTH	Returns the number of bytes used to represent any expression. Syntax: DATALENGTH ( <expression> )
@@ERROR	Returns the error number for the last Transact-SQL statement executed. Syntax: @@ERROR
ERROR_LINE	Returns the line number at which an error occurred that caused the CATCH block of a TRY ... CATCH construct to be run. Syntax: ERROR_LINE()
ERROR_MESSAGE	Returns the message text of the error that caused the CATCH block of a TRY...CATCH construct to be run. Syntax: ERROR_MESSAGE()
ERROR_NUMBER	Returns the error number of the error that caused the CATCH block of a TRY...CATCH construct to be run. Syntax: ERROR_NUMBER()
ERROR_PROCEDURE	Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of TRY...CATCH construct to be run. Syntax: ERROR_PROCEDURE()
ERROR_SEVERITY	Returns the severity of the error that caused the CATCH block of a TRY...CATCH construct to be run. Syntax: ERROR_SEVERITY()
ERROR_STATE	Returns the state number of the error that caused the CATCH block of a TRY...CATCH construct to be run. Syntax: ERROR_STATE()
fn_helpcollations	Returns a list of all the collations supported by SQL Server 2008. Syntax: fn_helpcollations()
fn_servershareddrives	Returns the names of shared drives used by the clustered server. Syntax: fn_servershareddrives()
fn_virtualfilestats	Returns I/O statistics for database files, including log files. Syntax: fn_virtualfilestats( { <database_id> | NULL }, { <file_id> | NULL } )
FORMATMESSAGE	Constructs a message from an existing message in sys.messages.  The functionality of FORMATMESSAGE resembles that of the RAISERROR statement.  However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing. Syntax: FORMATMESSAGE( <msg_number>, [ <param_value> [ , ...n ] ] )
GETANSINULL	Returns the default nullability for the database for this session. Syntax: GETANSINULL( [ '<database_name>' ] )
HOST_ID	Returns the workstation identification number. Syntax: HOST_ID()
HOST_NAME	Returs the workstation name. Syntax: HOST_NAME()
IDENT_CURRENT	Returns the last identity value generated for a specified table or view.  The last identity value generated can be for any session and any scope. Syntax: IDENT_CURRENT ( '<table_name>' )
IDENT_INCR	Returns the increment value (returned as numeric (@@MAXPRECISION, 0)) specified during the creation of an identity column in a table or view that has an identity column. Syntax: IDENT_INCR( '<table_or_view>' )
IDENT_SEED	Returns the original seed value (returned as numeric (@@MAXPRECISION, 0)) that was specified when an identity column in a table or a view was created.  Changing the current value of an identity column by using DBCC CHECKIDENT does not change the value returned by this function. Syntax: IDENT_SEED( '<table_or_view>' )
@@IDENTITY	Returns the last-inserted identity value. Syntax: @@IDENTITY
IDENTITY	This system function is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table.  Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE. Syntax: IDENTITY( <data_type>, [ , <seed>, <increment> ] ) AS <column_name>
ISDATE	Returns 1 if an input expression is a valid date or time value of datetime orsmalldatetime data types; otherwise, 0. Syntax: ISDATE ( <expression> )
ISNULL	Replaces NULL with the specified replacement value. Syntax: ISNULL ( <check_expression>, <replacement_value> )
ISNUMERIC	Determines whether an expression is a value numeric type. Syntax: ISNUMERIC ( <expression> )
NEWID	Created a unique value of type uniqueidentifier. Syntax: NEWID()
NULLIF	Returns a NULL value if the two specified expressions are equal. Syntax: NULLIF( <expression>, <expression> )
PARSENAME	Returns the specified part of an object name.  The parts of an object that can be retrieved are the object name, owner name, database name and server name. Syntax: PARSENAME ( '<object_name>', <object_piece> )
ORIGINAL_LOGIN	Returns the name of the login that connected to the instance of SQL Server.  This function can be used to return the identity of the original login in sessions in which there are many explicit or implicit context switches. Syntax: ORIGINAL_LOGIN()
@@ROWCOUNT	Returns the number of rows affected by the last statement.  If the number of rows is more than 2 billion, use ROWCOUNT_BIG. Syntax: @@ROWCOUNT
ROWCOUNT_BIG	Returns the number of rows affected by the last statement executed.  This functon operates like @@ROWCOUNT, except the return type of ROWCOUNT_BIG is bigint. Syntax: ROWCOUNT_BIG()
SCOPE_IDENTITY	Returns the last identity value inserted into an identity column in the same scope.  A scope is a module: a stored procedure, trigger, function, or batch.  Therefore, two statements are in the same scope if they are in the same stored procedure, function or batch. Syntax: SCOPE_IDENTITY()
SERVERPROPERTY	Returns property information about the server instance. Syntax: SERVERPROPERTY( '<property_name>' )
SESSIONPROPERTY	Returns the SET options settings of a session. Syntax: SERVERPROPERTY( '<set_option>' )
SESSION_USER	Returns the user name of the current context in the current database. Syntax: SESSION_USER
STATS_DATE	Returns the date that the statistics for the specified table were last updated. Syntax: STATS_DATE ( <table_id>, <stats_id> )
sys.dm_db_index_physical_stats	Returns size and fragmentation information for the data and indexes of the specified table or view.  For an index, one row is returned for each level of the B-tree in each partition.  For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition.  For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition.  If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. Syntax: sys.dm_db_index_physical_stats ( { <database_id> | NULL | 0 | DEFAULT } , { <object_id> | NULL | 0 | DEFAULT } , { <index_id> | NULL | 0 | -1 | DEFAULT } , { <partition_number> | NULL | 0 | DEFAULT } , { <mode> | NULL | DEFAULT }
SYSTEM_USER	Allows a system-supplied value for the current login to be inserted into a table when no default value is specified. Syntax: SYSTEM_USER
@@TRANCOUNT	Returns the number of active transactions for the current connection. Syntax: @@TRANCOUNT
UPDATE()	Returns a Boolena value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view.  UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions. Syntax: UPDATE ( <column_name> )
USER_NAME	Returns a database user name from a specified identification number. Syntax: USER_NAME( [ <user_id> ] )
XACT_STATE	Returns the user transaction state of a current running request.  XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. Syntax: XACT_STATE()