Scope of GOTO with GO

The GOTO statement is used to break the normal flow of execution to a particular label specified with GOTO statement. We will see this with examples, but the question of matter is whether GOTO statement goes/switch beyond the block defined with GO statement.  

Lets checkout with an example, here we are checking a condition in “if statement”, if the condition evaluates to true then we are instructing normal flow to break and go to a particular point i.e. VKM_POINT, but in this example we define the label VKM_POINT outside the GO block, now let’s see whether the GOTO has scope beyond the block where it is used.      

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

go

VKM_POINT:

      print 'Honey I was waiting for you'

Msg 133, Level 15, State 1, Line 5

A GOTO statement references the label 'VKM_POINT' but the label has not been declared.

So with the output we saw that GOTO is unable to switch the flow beyond the scope of batch. Lets modify the same example to have label point (VKM_POINT) inside the batch.

declare @tempVar int

set @tempVar = 10

if @tempVar =10

      goto VKM_POINT

VKM_POINT:

      print 'Honey I was waiting for you'

go

Honey I was waiting for you

Here with the output we can see that GOTO works properly when it has to jump to point which is inside the block where GOTO is used.

Conclusion: GOTO has scope only within a block

When programmer carry out rules, the ordinary human race slight.. J

Post Reference: Vikram Aristocratic Elfin Share

Variable Scope and CONTEXT_INFO

In SQL Server the variable which we declare in the block has scope to that block only, when you try to access that variable outside that scope, you won’t be able to access it. Let see with an example

Here we are creating two blocks, in first block we are declaring a variable @tempInt of INT type and initializing it with 5. And in second block, we are just trying to print the value of that variable.

declare @tempInt int

set @tempInt = 5

print @tempInt

go --Here we finished our first block

print @tempInt

go --Here we finished our Second block

5

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@tempInt".

From the output we can see the first Print which is present in first block where the variable was declared and initialized display its value but which we try to display the value of variable in second block it fires error saying, declare variable “@tempInt.

Conclusion: The scope of variable exists only to the block where it gets declared.

This problem can be solved using CONTEXT_INFO, context_info is a variable specific to a session of type varbinary(128), to access the value of context_info we have a function context_info(). You can also set your own value in context_info variable.

Session variable storing Varchar value

declare @strSource varbinary(50)

select @strSource = convert(varbinary(50),'My testing')

set context_info @strSource

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

My testing

Session variable storing int value

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),387)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print @strDestiInt

387

Session Variable scope

declare @strDesti varchar(50)

select @strDesti =  cast(cast(context_info() as binary(50)) as varchar)

print @strDesti       

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),300)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' from Ist Batch'

go      --Here we finished first block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IInd Batch'

declare @strSourceInt varbinary(50)

select @strSourceInt = convert(varbinary(max),400)

set context_info @strSourceInt

declare @strDestiInt int

select @strDestiInt = cast(cast(context_info() as binary(4)) as int)

print cast(@strDestiInt as varchar) + ' New value from IIst Batch'

go    --Here we finished second block

print cast(cast(cast(context_info() as binary(4)) as int) as varchar) + ' from IIIrd Batch'

go    --Here we finished third block

300 from Ist Batch

300 from IInd Batch

400 New value from IIst Batch

400 from IIIrd Batch

So from the output we can see that the first value which gets assigned to context_info variable was 300 and it is also accessable in second block too as we saw in second line of output then we modified the value of context_info variable in third block to 400, and again in third block we can access the modified value of context_info.

Conclusion: CONTEXT_INFO retains its value across blocks and u can also set new value to context info in any block.

When programmer do code, nothing annoys them so much.. J

Post Reference: Vikram Aristocratic Elfin Share

Nested Transaction III: Killing open transaction

Previous Article on Transaction:

Some witty finding on Nested Transaction with Rollback and Commit

Some witty finding on Nested Transaction II: Partial Rollback with Savepoint

We will see how to kill open transaction in a particular session, here below I m writing a block which will create two open transaction

For writing this script, I opened a session, just a new sql window, and executed the below script  

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

    

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)

Command(s) completed successfully.

With the help of DBCC OPENTRAN command, we can check the session and the transaction detail of open transaction. Here we firing DBCC command to check the session and transaction

dbcc opentran           

Transaction information for database 'Sparsh'.

Oldest active transaction:

    SPID (server process ID): 53

    UID (user ID) : -1

    Name          : outerTran

    LSN           : (1366:4126:1)

    Start time    : Jan  9 2014  8:51:38:750PM

    SID           : 0xa578602ded7cd44c96d1358f246a1c79

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So from the output of DBCC we can see that the open transaction “outerTran” exist in the session 53.

So now to kill this transaction we need to kill the session but you cannot kill a session begin inside the same session environment, so to kill a particular session, you need to open a new session and fire KILL command with session ID.

So here we opened a  new SQL window and fire the kill command with session id 53 where the transaction are in active state.

kill 53

Command(s) completed successfully

After killing the session if we fire DBCC OPENTRAN, we can see that there are no active transaction alive.

dbcc opentran

No active open transactions.

Every code is genius at least once in his life. The real geniuses are programmers who keep those codes bright everlastingly.

Post Reference: Vikram Aristocratic Elfin Share

Some witty finding on Nested Transaction II: Partial Rollback with Savepoint

Read Prev Article: Some witty finding on Nested Transaction with Rollback and Commit

Here in this article we will see how we can rollback a portion of transaction keeping other part as it is for commit. For revelation we are again taking the same EMP table, let see first, how many records are present in it.

  

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

(5 row(s) affected)

Now I am writing a block which is nested by two transaction, in the inner transaction after inserting record with emp_id 8, I m trying to rollback the inner transaction while keeping the first (outer) transaction as it is for commit.

set nocount on

go

begin tran

      insert into emp values(7,'Sanjana',2)

      begin tran

            insert into emp values(8,'Monica',2)

            rollback tran

commit tran

Msg 3902, Level 16, State 1, Line 6

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

(5 row(s) affected)

Here we saw, the first call to ROLLBACK TRAN in inner transaction rollback both the transaction the outer and inner one. So there must be no insert into the table emp. We can also check this using @@trancount.

Let’s print @@trancount in the same script.

set nocount on

go

begin tran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran

Trancount : 1

Trancount : 2

Trancount : 0

Msg 3902, Level 16, State 1, Line 9

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So after the rollback fired, both the opened transaction closed and we get @@trancount as 0.

Now let’s define the transaction with name so that we can explicitly mention which transaction to rollback

set nocount on

go

begin tran   outerTran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran InnerTran

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran  InnerTran

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran outerTran

Trancount : 1

Trancount : 2

Msg 6401, Level 16, State 1, Line 7

Cannot roll back InnerTran. No transaction or savepoint of that name was found.

Trancount : 2

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

7           Sanjana    2

8           Monica     2

So we can rollback on inner transaction has no effect and the out transaction committed both the insert in spite of having rollback on second transaction.

I am deleting employee with emp_id 7 and 8

delete emp where emp_id=7 or emp_id=8

Here I am using Savepoint, this will allow to rollback some part of transaction leaving other part as it is for commit. One more thing, one can’t use savepoint without transaction. Let’s see, how we are using savepoint in our earlier code..

set nocount on

go

begin tran   outerTran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran InnerTran

            save tran ManishPoint

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran  ManishPoint

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran outerTran

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

7           Sanjana    2 

Now you can see the inner transaction which was trying to insert employee with id 8 get rollbacked and leaving other part of transaction as it is to commit.

Watch your thought, they became logic

Watch your logic, they became code

Watch your code, they became program

Watch your program, crop up new thought

SDLC…cont..

Post Reference: Vikram Aristocratic Elfin Share

Some witty finding on Nested Transaction with Rollback and Commit

When we have transaction block defined inside transaction block, we call it as Nested transaction, and here in this article we will see how Rollback and Commit works with Nested transaction. Let’s start with Rollback

   

Nested Transaction with Rollback

We are creating a table with a single column for our demo.

create table TAB_NestedTransaction_Demo

(col1 int)

Command(s) completed successfully.

Now lets start with our transaction block, here we are creating a transaction block named “firstTran”, inside it we are first inserting a record in newly created table “TAB_NestedTransaction_Demo” thereafter again we are creating one more transaction block named “secondTran” in inside the second transaction block we are inserting one more record in “TAB_NestedTransaction_Demo” table. Then we are rollbacking the second transaction and checking the transaction count (number of active transaction)

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

     

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)           

      rollback tran secondTran

      select @@TRANCOUNT     

Msg 6401, Level 16, State 1, Line 6

Cannot roll back secondTran. No transaction or savepoint of that name was found.

No of Active Tran

-----------------

2

The above script throw an error while trying to rollback the inner transaction “secondTran”. And we can see the transaction count comes out as 2. That means none of the transaction gets rollbacked even after explicitly rollbacking innner transaction “secondTran”.Lets now execute the below lines and see the finding

rollback tran firstTran

go

select @@TRANCOUNT as [No of Active Tran]

No of Active Tran

-----------------

0

These two lines successfully gets executed and we can see transaction count comes out to be zero now.

Conclusion : from the above example we can see that  SQL server will allow you to rollback only the outer transaction and rollback of outer transaction will rollback all nested transactions and hence making the transaction count to zero.

Nested  Transaction with Commit

Lets now check out nested transaction with COMMIT, here we can see in the below code block we open first transaction firstTran then we inserted a record in TAB_NestedTransaction_Demo then we are opening one more transation inside the outer one and inserting one more record to the same table, thereafter we are committed second transaction. Then we rollback first transaction.

   

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

      begin tran secondTran

            select @@TRANCOUNT [No of Active Tran from Inner]

            insert into TAB_NestedTransaction_Demo values(2)

      commit tran secondTran

      select @@TRANCOUNT [No of Active Tran from Outer]

rollback

[No of Active Tran from Inner]

-----------

2

[No of Active Tran from Outer]

-----------

1

Now lets query TAB_NestedTransaction_Demo.

select * from TAB_NestedTransaction_Demo

col1

-----------

(0 row(s) affected)

To our sock what we saw there is no records which get inserted in the table even after committing the inner transaction after inserting record into the table.

One more finding which we can see is, after committing the inner transaction, the transaction count reduced to one. Which says that the inner transaction is committed, but when doing select query on table, after executing last rollback statement, we found no records in the table?

Conclusion:  There is no effect in committing the inner transaction in Nested Transaction. And you cannot rollback inner transaction.  

Ask a programmer about his experience of living with code, he will say love, grace and beautiful…   

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching VI - Forced Parameterization in Adhoc Queries

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1
• Plan Caching IV - Simple Parameterization in Adhoc Query Processing
• Plan Caching V - Disadvantage of Simple Parameterization

In the last article we saw that the datatype of constant is selected on fly depending upon the constant value by SQL Server, due to which the cached Prepared Parameterized plan is not been used by a similar query with constant little different.

Just check this, below example.  

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

select * from emp where emp_id = 1

go

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

From the output we can see that the constant used in query where predict is being treated as tinyint. So the datatype is of the constant is being selected on fly by SQL Server while making a prepared Parameterized cache plan.

Now let’s query the same select with contact value ranging out of tiny data type.

select * from emp where emp_id = 700

go

 

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see from the output that it has not used the Prepared plan which gets generated when we executed query with predicate constant value as 1 and generate a new prepared plan and cached in memory; this time the constant is replaced by parameter and the type for the parameter is choosed as smallInt unlike tinyint in previous case.

So we get two prepared queries plan with two different parameter data type. So SQL Server is not making use of existing Parameterized cached plan.

The only way to force SQL Server to use the same data type  for both the queries is to enable PARAMETERIZED FORCED for the database.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

alter database sparsh set parameterization forced;

go

Here we have set PARAMETERIZATION FORCED enabled. Once this is done SQL Server treats every kind constants as just a set of parameters.

Now since we have enabled PARAMETERIZATION FORCED. Lets run both the select query and query the dm_exec_cached_plans to check the output.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 700

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go   

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

2         Compiled Plan Prepared (@0 int)SELECT * FROM emp WHERE emp_id=@0

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see that no new Prepared plan gets generated, instead the same prepared plan is referred by the second query with emp_id = 700. And the data type chosen by SQL Server for constant is INT which satisfy both the constant.

Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.

If my girl friend judge my love to code is like putting them in a strait jacket and kicking them down a flight, then yes.. I have deep love to my code K   

Post Reference: Vikram Aristocratic Elfin Share