Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Twitter Profile and Followers
Built-in Functions
Home > Functions > SQL Server System Functions
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>' )

Function Description
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 or smalldatetime 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()

Related Articles :