SQL Server system function (T-SQL) and brief description.

System Functions (T-SQL)

@@ERROR : Returns the error number for the last Transact-SQL statement executed.
@@IDENTITY : Returns the last-inserted identity value.
@@ROWCOUNT : Returns the number of rows affected by the last statement.
@@TRANSCOUNT : Returns the number of active transactions for the current connection.
APP_NAME : Returns the application name for the current session if set by the application.
CASE : Evaluation a list of condition and returns one of multiple possible result expression.
CAST : (expression as data_type) / CONVERT : Convert an expression of one data type to another.
COALESCE (expression [ ,...n]) : Returns the first non null expression among its arguments.
CURRENT_TIMESTAMP : Returns the current date and time. ANSI SQL equivalent to GETDATE.
CURRENT_USER : Returns the name of the current user. Equivalent to USER_NAME().
DATALENGTH (Expression) : Returns the number of bytes used to represent any expression.
FORMATMESSAGE (msg_number , [param_value [,...n]]) : Constructs a message from an existing message

in sys.messages and returns the formatted message for further processing. 
GETANSINULL : Returns the default nullability for the database for this session.
HOST_ID : Returns the workstation identification number.
HOST_NAME : Returns the workstation 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.
IDENT_SEED : Returns the seed value (returned as numeric (@@MAXPRECISION,)) that was specified when

 an identity column in a table or a view that has an identity column was created.
IDENTITY : to insert an identity column into a new table.
ISDATE (expression) : Determines whether an input expression is a valid date.
ISNULL (expression , replacement_value) : Replaces NULL with the specified value.
ISNUMERIC (expression) : Determines whether an expression is a valid numeric type.
NEWID : Creates a unique value of type unique identifier.
NULLIF (expression , expression) : Returns the null value if the two specified expression are equal.
PARSENAME (object_name , object_piece) : Returns the specified part of an object name. Parts of an object

that can retrieved are the object name, owner name, database name and server name.
PERMISSIONS ([objectid [,'column']]) : returns a value containing a bitmap that indicate the statement,

object or permissions of the current user.
SESSION_USER : Returns the user name of the current context in the current database.
STATS_DATE : Returns the date that the statistics for the specified index were last updated.
SYSTEM_USER : Allow a system-supplied value for the current login to be inserted into a table

 when no default value is specified.
USER_NAME ([ID]) : returns a database user name from a specified identification number.