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.

Transactions in SQL Server

Transactions

A transaction groups a set of task into a single execution unit. Every transaction begins with a specific task and ends when all the tasks in the group successfully complete. If the execution of any of the tasks is fails, the transaction is fails. Therefore, we can say a transaction has only two results: success or failure. Any incomplete steps result in the failure of the total transaction.

Properties of Transactions

Every Transaction has the following four standard properties and in short form generally called ACID:

1. Atomicity: Ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is will be failed and previous operations are rolled back to their previous state.
2. Consistency: Ensures that the database properly changes its state based on a successfully committed transaction.
3. Isolation: Enables transactions to work independently.
4. Durability: Ensures that the effect of a committed transaction persist in case of any failure.

Transaction Control

The following commands are used to control transactions:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

Example

      BEGIN TRAN

      BEGIN TRY

            --INSERT INTO SQL statement

            --INSERT INTO SQL statement

            COMMIT TRAN

      END TRY    

      BEGIN CATCH

            ROLLBACK TRAN --For Error

            SELECT ERROR_NUMBER() AS ErrorNumber,

            ERROR_SEVERITY() AS ErrorSeverity,

            ERROR_STATE() AS ErrorState,

            ERROR_PROCEDURE() AS ErrorProcedure,

            ERROR_LINE() AS ErrorLine,

            ERROR_MESSAGE() AS ErrorMessage;

      END CATCH