System Functions

---

System Functions in SQL Server

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

System functions are listed out and explained as follows:

Function	Description
APP_NAME	Returns the application name for the current session if set by the application.

Syntax:

APP_NAME()

Function	Description
CASE	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

Function	Description
CAST and CONVERT	Converts an expression of one data type to another.

Syntax for CAST:
CAST ( <expression> AS <data_type> [ ( <length> ) ] )

Syntax for CONVERT:
CONVERT ( <data_type> [ ( <length> ) ], <expression> [ , <style> ] )

Function	Description
COALESCE	Returns the first nonnull expression among its arguments.

Syntax:

COALESCE ( <expression> [, ... n ] )

Function	Description
COLLATIONPROPERTY	Returns the property of a specified collation.

Syntax:

COLLATIONPROPERTY ( '<collation_name>', '<property_name>' )

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

Function	Description
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

Function	Description
CURRENT_USER	Returns the name of the current user.This function is equivalent to USER_NAME().

Syntax:

CURRENT_USER

Function	Description
DATALENGTH	Returns the number of bytes used to represent any expression.

Syntax:

DATALENGTH ( <expression> )

Function	Description
@@ERROR	Returns the error number for the last Transact-SQL statement executed.

Syntax:

@@ERROR

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

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

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

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

Function	Description
ERROR_SEVERITY	Returns the severity of the error that caused the CATCH block of a TRY...CATCH construct to be run.

Syntax:

ERROR_SEVERITY()

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

Function	Description
fn_helpcollations	Returns a list of all the collations supported by SQL Server 2008.

Syntax:

fn_helpcollations()

Function	Description
fn_servershareddrives	Returns the names of shared drives used by the clustered server

Syntax:

fn_servershareddrives()

Function	Description
fn_virtualfilestats	Returns I/O statistics for database files, including log files.

Syntax:

fn_virtualfilestats( { <database_id> | NULL }, { <file_id> | NULL } )

Function	Description
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 ] ] )

Function	Description
GETANSINULL	Returns the default nullability for the database for this session.

Syntax:

GETANSINULL( [ '<database_name>' ] )

Function	Description
HOST_ID	Returns the workstation identification number.

Syntax:

HOST_ID()

Function	Description
HOST_NAME	Returs the workstation name.

Syntax:

HOST_NAME()

Function	Description
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>' )

Function	Description
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>' )

Function	Description
@@IDENTITY	Returns the last-inserted identity value.

Syntax:

@@IDENTITY

Function	Description
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>

Function	Description
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> )

Function	Description
ISNULL	Replaces NULL with the specified replacement value.

Syntax:

ISNULL ( <check_expression>, <replacement_value> )

Function	Description
ISNUMERIC	Determines whether an expression is a value numeric type.

Syntax:

ISNUMERIC ( <expression> )

Function	Description
NEWID	Created a unique value of type uniqueidentifier.

Syntax:

NEWID()

Function	Description
NULLIF	Returns a NULL value if the two specified expressions are equal.

Syntax:

NULLIF( <expression>, <expression> )

Function	Description
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> )

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

Function	Description
@@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

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

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

Function	Description
SERVERPROPERTY	Returns property information about the server instance.

Syntax:

SERVERPROPERTY( '<property_name>' )

Function	Description
SESSIONPROPERTY	Returns the SET options settings of a session.

Syntax:

SERVERPROPERTY( '<set_option>' )

Function	Description
SESSION_USER	Returns the user name of the current context in the current database.

Syntax:

SESSION_USER

Function	Description
STATS_DATE	Returns the date that the statistics for the specified table were last updated.

Syntax:

STATS_DATE ( <table_id>, <stats_id> )

Function

Description

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 }

Function	Description
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

Function	Description
@@TRANCOUNT	Returns the number of active transactions for the current connection.

Syntax:

@@TRANCOUNT

Function	Description
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> )

Function	Description
USER_NAME	Returns a database user name from a specified identification number.

Syntax:

USER_NAME( [ <user_id> ] )

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