Wednesday, August 7, 2013

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.

Cumulative Sum in SQL

Running Total in SQL

Cumulative Sum in SQL

Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.

SL          INT,
GroupName   NVARCHAR(100),
Amount      DECIMAL(18,2)

INSERT INTO @Temp VALUES(1,'Number-1',5)
INSERT INTO @Temp VALUES(2,'Number-2',8)
INSERT INTO @Temp VALUES(3,'Number-3',2)

SELECT      T1.SL,
            SUM(T2.Amount) as CumulativeSum
            @Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount

If we run the above code we will get the cumulative output.