Nested Transaction in Sql Server

In previous article, we learnt about transaction

In this article we will see how nested transactions work.

The variable @@TranCount is used check the count of running transaction(s)

In nested transactions, the variable @@TranCount can be used to check the count of level of nested transactions.

In nested transaction, commit works only on the same level as of transaction (current transaction) i.e. all the statements under that level of transaction will be committed where as rollback always works on parent transaction i.e. all the statements of all the levels will be rolled back.

NOTE: Rollback reverts all the changes even if any nested transaction was committed.

Let's take an example where we will have two level of transaction and we will commit the 2nd level transaction and rollback the 1st level transaction.

begin tran
print 'Tran Count on 1st level : ' + cast(@@trancount as varchar)
    insert into TableA select 'EMP003','Ritesh'   
    begin tran
    print 'Tran Count on 2nd level : ' + cast(@@trancount as varchar)
        insert into TableA select 'EMP004','Abhay'       
    commit tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
rollback tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from TableA

OUTPUT

Look at the output, we inserted a row in the inner transaction (2nd level) and committed it but even then it is not inserted. This is because we have rolled back the outer transaction (1st level) which even rolled back the committed transaction (2nd level)

In the next example, lets try to rollback the inner transaction and then commit the outer one

begin tran
print 'Tran Count on 1st level : ' + cast(@@trancount as varchar)
    insert into TableA select 'EMP003','Ritesh'   
    begin tran
    print 'Tran Count on 2nd level : ' + cast(@@trancount as varchar)
        insert into TableA select 'EMP004','Abhay'       
    rollback tran
    print 'Tran Count after 2nd leve commit : ' + cast(@@trancount as varchar)
commit tran
print 'Tran Count after 1st level rollback : ' + cast(@@trancount as varchar)
select * from TableA

OUTPUT

See, we got an error here. This is because as I have already mentioned above that rollback works on parent level so it has rolled back all the transaction(s) so there are no transaction(s) left to commit or rollback.

Transaction in Sql Server

In this article, I am sharing the concept of transaction in SQL Server and how it is implemented.
Transactions run as a group of commands or queries as single execution unit. It ensures either all the commands or queries will be executed or none of them.

Once transaction is in open state, it needs to be either committed or rolled back using following commands

• Begin Tran: To start a transaction.
• Commit Tran: To commit all the executed statements.
• Rollback Tran: To rollback all the executed statements.

Let's create a table and see how transaction works with some examples

create table TableA( empid varchar(10), empname varchar(10))

Let's insert some rows in the table within the transaction and rollback the transaction

begin tran
    insert into TableA
    select 'EMP001', 'Sandeep' union all
    select 'EMP002', 'Abhay'   
rollback
select * from TableA

OUTPUT

Look at the output, we inserted 2 rows but there is no row in the table as we have rolled back the transaction.

Now lets run the same statements with commit.

begin tran
    insert into TableA
    select 'EMP001', 'Sandeep' union all
    select 'EMP002', 'Abhay'   
commit
select * from TableA

OUTPUT

Look at the output now, we run the same statements with commit and this time the rows are inserted

In the next article, we will see how how nested transaction works.

jQuery Interview Questions - Part 1

Q: What is jQuery?
jQuery is a light weight JavaScript library which provides fast and easy way of HTML DOM traversing and manipulation, event handling, and client side animations, etc. One of the greatest features of jQuery is that it supports an efficient way to implement AJAX applications because of its light weight nature and make normalize and efficient web programs.

Q: Is jQuery a library for client scripting or server scripting?
jQuery is client scripting library.

Q: Which sign does jQuery use as a shortcut for jQuery?
$(dollar) sign.

Q: What does dollar Sign ($) means in JQuery?
Dollar Sign is nothing but it’s an alias for JQuery. Take a look at below jQuery code

$(document).ready(function(){  
});

Q: What are jQuery Selectors? 
jQuery Selectors are used to

• find out DOM elements.
• find the elements by ID, Class, Tag Name etc.

jQuery selectors always start with dollar sign and parenthesis : $()

Some examples of jQuery Selectors
Select element by tag name

$(div)

This will select all the div elements

Select elements by id

$(#mydiv)

This will select div with id "mydiv"

Select elements by class

$("clsdiv")

This will select all the div elements with class "clsdiv"

Q: What are different type of selectors in jQuery?
There are 3 types of selectors in jQuery

• CSS Selector
• XPath Selector
• Custom Selector

Q: Does the jQuery html() method work for both HTML and XML documents?
jQuery html() only works for HTML.

Q: What does $("div") will select?
It will select all the div element in the page.

Q: What does $("div.parent") will select?
All the div element with parent class.

Q: What is the name of jQuery method used for an asynchronous HTTP request?
jQuery.ajax()

Next >>

Encrypt Stored Procedure

In SQL Server, stored procedures are generally created to hold the business logic of the application. For security reasons, you may want to hide your business logic from the end users.

With Encryption keyword is used to encrypt the definition of the stored procedure.

NOTE: Once stored procedure is encrypted it is not possible to get the definition of the stored procedure.

Before encrypting, developers have to save the original definition as a backup as it will be referred in case you need to check the business logic or in case any modification is required in the business logic.

For an example, Let's create an encrypted stored procedure using With Encryption keyword

create procedure encryptedProc with encryption
as
begin
    select 1
end

Now let's try to view the text/definition of stored procedure

sp_helptext encryptedProc

OUTPUT