Wednesday, August 7, 2013

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