July 10, 2008 at 8:04 am
hi
can any one give me detailed anlysis of using GO in general
and also in stored procedure....Does it affect performance??
July 10, 2008 at 8:23 am
According to the BOL
"SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO. "
July 10, 2008 at 9:09 am
GO is not a T-SQL command. It's understood by client tools only, and signals the end of a batch.
You can't put a GO into a stored procedure, because as soon as the client tool sees the GO, it interprets that as the end of the stored procedure and the rest of the statements are considered to be in a separate batch
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2008 at 9:16 am
In stored proc we place the Go only at the end of the procedure.
Go marks the end of batch and sends the part from last started transaction point upto this point as one transaction batch.
July 10, 2008 at 10:38 am
Hi
“GO” is a batch terminator. Technically speaking, the GO command is not even a part of the Transact-SQL language. It is really a command used by the SQLCMD, OSQL and ISQL utilities that can also be used within Query Analyzer and the Query Editor window.
Thanks -- Vj
July 11, 2008 at 9:02 am
The other thing that sometimes comes in useful with GO is that you can put a number after it, and it will run the commands before GO that number of times.
If you want to speed-test a proc by running it repeatedly with random parameters, this comes in very handy.
declare @Param int
select @Param = checksum(new_id())
set statistics io on
set statistics time on
exec dbo.proc @Param
set statistics time off
set statistics io off
print replicate('=', 25')
go 100
That will run the proc 100 times with (semi) random inputs, which can be a good way to test run time and io characteristics.
Set this up in two or more connections at the same time, from different machines, and you can even do some load testing of a limited sort, and test for deadlocks pretty well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply