GO and batch execution multiple times.

Go is a useful command especially when you want to execute a batch multiple times. For time being we can take an example of inserting multiple dummy records in a table, with just ONE insert statement. Also read previous articles on GO

GO and the Stored Procedure

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

Let’s create a temporary table for this purpose.

use testDemo

go --First Batch

create table #tempTable

(tempID uniqueidentifier,

tempMonth int,

tempDateTime datetime )

GO -- Second Batch

Command(s) completed successfully.

These batches will just execute these separate batches for once as default value for GO is one. Lets insert 100 records in our temporary table with just one insert statement

insert into #tempTable (tempID, tempMonth, tempDateTime)

select NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()

GO 10

Batch execution completed 10 times.

“Go 100” will execute this insertion batch for ten times, and exultantly 10 records will be inserted. That’s what I call it “Power of GO”. Check this.

select * from #tempTable

tempID                               tempMonth   tempDateTime

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

C2055F4A-0FF9-4A2C-BB9F-9CC665EEC009 3           2013-03-25 14:30:37.830

4322FE78-4494-45B3-8923-5D9CD5F030CA 3           2013-03-25 14:30:37.933

86D89223-530B-423E-AAB1-8E65601D821B 8           2013-03-25 14:30:37.933

7E1A8812-9A8E-412D-8EE4-4FBE02FB4CBB 8           2013-03-25 14:30:37.937

917C89A6-1DFB-4375-A6E3-5C15F556AFCB 6           2013-03-25 14:30:37.940

7DEBDD5D-E2B9-45BB-9D65-D911DA32F431 7           2013-03-25 14:30:37.940

CC124777-7AE4-47A8-B7CA-0EE566D01465 5           2013-03-25 14:30:37.940

6A0E6F7C-47A6-4F2A-A8C0-58E4A0B498E2 4           2013-03-25 14:30:37.947

CB22E285-689A-466A-A7EB-E0734ED1435B 3           2013-03-25 14:30:37.950

7102D516-03AC-4EE9-93C8-820477E645D6 5           2013-03-25 14:30:37.953

(10 row(s) affected)

Now drop the temporary table

drop table #tempTable

Conclusion: GO can be used for multiple times batch execution.

Honk if you hate coding

Post Reference: Vikram Aristocratic Elfin Share

GO and the Stored Procedure

In this article, we will see how GO can have an effect on writing stored procedure, before that go through my previous post 

GO, is it a SQL command, a jump to pointer or what exactly?

GO and the Scope of variable.

We will directly jump into the example to see the effect of go in stored procedure, Now we are creating the stored procedure MyManager, and if you notice after end block of stored procedure, there is a select statement and a GO statement proceed with it.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

select 'Oh come on! This is absolutly lie'

go

Command(s) completed successfully.

Now if we try to execute the procedure..

exec MyManager

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

My Manager is the best one.

(1 row(s) affected)

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

Oh come on! This is absolutly lie

(1 row(s) affected)

Say, if you show this stored procedure’s output to your Manager, you better have a new job lined up.

What happened? You intended to show him only the first line, but the output of the second select also is displayed.

Why? Because the whole code is scripted into the syscomments table until a GO command is found.

Now lets drop the procedure and recreate the same

drop proc MyManager

Command(s) completed successfully.

Procedure lets recreate the same procedure, where select statement appears after the GO statement.

Create procedure MyManager

as

begin

     select 'My Manager is the best one.'

end

go

select 'Oh come on! This is absolutly lie'

Now if we execute the procedure, we gets our desired result.

exec MyManager

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

My Manager is the best one.

(1 row(s) affected)

Conclusion: If the stored procedure isn’t immediately followed by a GO command, an executable statement that appears after the stored procedure will be executed together with the stored procedure, which as we have seen can have unintended consequences.

I just like to code and be happy.

Post Reference: Vikram Aristocratic Elfin Share

GO and the Scope of variable.

We  will be see the scope of a local variable and it affect with the GO statement, before that go through my previous post  GO, is it a SQL command, a jump to pointer or what exactly?

At the present let’s discuss about the scope of a local variable, by execute the following set of t-SQL statements

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

select @strMsg as GoMessage

GoMessage

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

Testing scope with go

(1 row(s) affected)

We can see, it executed properly and the value of the local variable @strMsg which was set, displays accurately with select statement.  

Now let’s add GO statement in between set and select statement and see the result. Will it make any difference let’s see

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

go

select @strMsg as GoMessage

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@strMsg".

Here to our shock we found error saying @strMsg not declared… :-O, How come, we have already declare and set @strMsg before displaying it, then what is the problem?

Reason: Encounter of GO statement  send signal to to Sql Server instance so to send the current batch  (T-SQL statements between  GO commands)  and execute Independently.

This means, the satement before go will form a batch and execute independently and the statement after go to the next encounter go will form the next batch and executes seperatly.

In our example, 2 batches gets created and executes seperately

First batch is this, which executes independently

declare @strMsg varchar(25)

set @strMsg = 'Testing scope with go'

where as, Second batch is this, which ssms sent as a independent execution.

select @strMsg as GoMessage

So due to independent execution of second batch, it throws error, because we are querying a string value, without declaring it.

Wrapping up : GO is not a Transact-SQL statement; it’s just a command to send signal so to send the current batch  (T-SQL statements between two GO commands) of T-SQL statements to Sql Server instance.

A fair code ought to be pursued by the deed in silence.

Post Reference: Vikram Aristocratic Elfin Share

GO, is it a SQL command, a jump to pointer or what exactly?

Yesterday it happened one of my very good friend asked me, why it is necessary to write go statement in an arrangement like this

alter table tblPopulation

add street varchar(10) 

go

select country,state,street,population from tblPopulation

Let’s find out what the picture behind GO statement.

To understated this lets first execute the same statement without having GO in between the alter and select statement.

alter table tblPopulation

add street varchar(10)

select country,state,street,population from tblPopulation

Msg 207, Level 16, State 1, Line 4

Invalid column name 'street'.

Msg 207, Level 16, State 1, Line 4

Invalid column name 'population'.

From the error message it is clear that the new field which we are adding to the existing table tblPopulation has not been added yet and the SQL parser parse the select command, due to which it didn’t find the new coloum population and throws a error.

Where as if we add go in between select and alter statement, we will be able to execute the statement with all ease.  

alter table tblPopulation

add street varchar(10)

go

select country,state,street from tblPopulation

country state     street

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

India   Delhi     NULL

India   Delhi     NULL

India   Delhi     NULL

India   Karnataka NULL

(4 row(s) affected)

This shows, If you put a GO between the two statements, it'll work, because SSMS won't parse and verify the whole statement ahead of time - it will do the first part, and then only parse the second (after the GO).

LEARNING: They're not strictly required - they're just instructions for the SQL Server Management Studio to execute the statements up to this point now and then keep on going. GO is not a T-SQL keyword or anything - it's just an instruction that works in SSMS.

Conclusion: Sometimes, you need a GO - e.g. if you add a column to a table, and then want to select it again, you need to have a GO between the adding of the column, and the query of it.

Code it and live the moment to the utmost satisfaction

Post Reference: Vikram Aristocratic Elfin Share

Feeling pain in importing data to index table; here it is dub way out

Disable Index and Insert!!! Is this the solution???

In order to speed the import of our large amount of data we want to disable all non-clustered indexes.  The easiest way of doing this is to disable the clustered index since whenever we disable a clustered index; it disables all the non-clustered indexes on the table as well. 

NOTE: An interesting side-effect that saves us the time of disabling each non-clustered index individually but presents us with a serious problem: once a clustered index is disabled, users can not access the underlying table data.

Let me say that again: once a clustered index is disabled, users can not access the underlying table data. 

So, we want the ease of disabling all indexes at once but need to mitigate the rather pesky side-effect of losing access to the data that occurs when one disables a clustered index.  The way to do that is to disable the clustered index, then re-enable it, leaving the non-clustered indexes disabled.

Lets fuel up our discussion by stimulate the same scenario, we are create table  InsertNdisableIndexDemo and defining cluster index on primary key and non cluster index on fName, sName and education

create table InsertNdisableIndexDemo

(id int identity(1,1) primary key clustered,

fName varchar(10),

sName varchar(10),

age int,

education varchar(10))

command(s) completed successfully.

create nonclustered index nonClusterd_fName ON InsertNdisableIndexDemo (fName)

create nonclustered index nonClusterd_sName ON InsertNdisableIndexDemo (sName)

create nonclustered index nonClusterd_education ON InsertNdisableIndexDemo (education)

Command(s) completed successfully.

Now we have indexes on place for InsertNdisableIndexDemo table, lets query the sys.indexs table to see all the indexes and the status of each index.

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  0

nonClusterd_sName               NONCLUSTERED  0

nonClusterd_education           NONCLUSTERED  0

(4 row(s) affected)

We can see from the result of query that all indexes are in place and are in enable status. Now our task is to diable the indexes so as to ease the work of importing data to sql server, for this we will be taking the below approach, let find out each step

Follow this procedure:

1. Disable all indexes on a table by disabling the clustered index
2. Rebuild only the clustered index (since a rebuild is the method for re-enabling an index), leaving the non-clustered indexes disabled
3. Check disabled indexes
4. Check to make sure no clustered indexes are disabled in the database
5. Import the data
6. Rebuild all indexes (since a rebuild is the method for re-enabling an index) on a table

Disable all indexes on a table by disabling the clustered index

We are now disabling our cluster index, which will ultimately all non cluster index will get disable.

  

alter index PK__InsertNd__3213E83F403A8C7D on InsertNdisableIndexDemo disable

Warning: Index 'nonClusterd_fName' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

Warning: Index 'nonClusterd_sName' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

Warning: Index 'nonClusterd_education' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

We can check out the indexes status by querying the sys.indexes table

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     1

nonClusterd_fName               NONCLUSTERED  1

nonClusterd_sName               NONCLUSTERED  1

nonClusterd_education           NONCLUSTERED  1

(4 row(s) affected)

As to our discussion all the non cluster indexes are disabled by disabling the cluster index.  

Now if we try to insert data to the table, it will give error

insert into InsertNdisableIndexDemo

select 'Akansha','Patnaik',24,'BE'

Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index 'PK__InsertNd__3213E83F403A8C7D' on table or view 'InsertNdisableIndexDemo' is disabled.

Reason: Cluster index is disable

Rebuild clustered index

alter index PK__InsertNd__3213E83F403A8C7D on InsertNdisableIndexDemo rebuild

Command(s) completed successfully.

This will rebuild only the clustered index, re-enabling access to the data. Now lets check the disable index

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  1

nonClusterd_sName               NONCLUSTERED  1

nonClusterd_education           NONCLUSTERED  1

(4 row(s) affected)

Now, checking the disabled indexes on InsertNdisableIndexDemo shows that only the non-clustered indexes are disabled.

 Once all non cluster index gets disabled, you can go ahead and perform your import/insert of data to your table.

insert into InsertNdisableIndexDemo

select 'Akansha','Patnaik',24,'BE'

union all

select 'Pratiksha','Sharma',24,'Mtec'

union all

select 'Aadhya','Mohapatra',24,'BE'

union all

select 'Prajna','Das',24,'Btec'

union all

select 'Nandani','Ray',24,'MBA'

union all

select 'Apali','Mohanty',24,'CA'

union all

select 'Niharika','Patnaik',24,'BE'

(7 row(s) affected)

Once the insert completes we can rebuild all indexes which will both re-enable the indexes and update them with the new data.

alter index all on InsertNdisableIndexDemo rebuild

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  0

nonClusterd_sName               NONCLUSTERED  0

nonClusterd_education           NONCLUSTERED  0

(4 row(s) affected)

Just code,have fun. Enjoy the game