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.

Transactions in SQL Server


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

            --INSERT INTO SQL statement
            --INSERT INTO SQL statement
            COMMIT TRAN
      END TRY    
            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;

Stored Procedure

Stored Procedure

Stored Procedure is one of the powerful parts of SQL Server. Basically it is a group of T-SQL statements which is complied and stored inside the SQL Server.

As a result we get some advantages like:
1. Secure due to encryption.
2. We can use repeatedly after once compile.
3. Reduces data pass over a network.
4. Secured raw data because they are accessible by only stored procedure.
Basis Syntax of Stored Procedure
Input parameter DataType
Output parameter DataType Output
--Variable Declaration 
  @parameter DataType
--SQL Statements
In the stored procedure we can use SELECT, INSERT, DELETE and UPDATE these four basic query statements. For example, we can use the following query from application.
SELECT EmpID,EmpName,Cell 
FROM Employee
WHERE DeptID = 1
So, every time this query will be compiled. But if we make a stored procedure then it will compile one time and can be used as many times as required. So, stored procedure is more efficient than general query.
Simple Stored Procedure
@DID int 
 SELECT EmpID,EmpName,Cell 
 FROM Employee
In above Stored Procedure @DID is a parameter.
Execute the Stored Procedure
EXEC sp_Employee 1
EXECUTE sp_Employee 1

Random Number in SQL

How to generate unique random number in SQL?

Random Number in SQL
SQL Server has a built-in function to generate random number. The function is RAND(). It is a mathematical function.  It returns a random float value between 0 and 1.  We can also use an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.To use it, we need to use a simple SELECT statement as follows:

SELECT RAND() AS [RandomNumber]

The output of the RAND() function will always be a value between 0 and 1.  If we want to generate a random integer number, we have to multiply it by the maximum value that we want to generate and then get rid of the decimal places.  By casting to integer we can get rid of the decimal places.

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

If we pass seed parameter value then the RAND() function will always return same value. 

SELECT RAND(1) AS [RandomNumber]

The NEWID() Way
Here's a different way to generate a random number without using the RAND() function. We can use NEWID system function to generate a random numeric value.


The NEWID function returns a unique value of unique identifier data type. To convert it into integer first we have to convert it into VARBINARY then integer. The resulting integer value can be positive and negative. It we want only a positive value then we need to use the absolute value mathematical function ABS. 

Method 1 : Generate Random Numbers (Int) between Range
-- Variables for the random number

-- Random number between 1 and 999
SET @Lower = 1     -- The lowest random number
SET @Upper = 999 -- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower),0)
SELECT @Random