Tuesday, September 25, 2012


Recently at an  SQLSaturday ,I learnt a trick - using GO to execute a statement or block of statements in a loop.

GO is a batch separator and it is not a T-SQL command.A long time back, I used to change the batch separator option to my name instead of GO and have a unwary developer look at SQL Server execute a piece of code with my name on it and GO crazy .

Since SQL Server 2005,the GO syntax has been changed to GO [count], where count is an integer.
Specifying a value for [count] would cause the block of code preceding the GO to be executed [count] times.

For example lets say we have a block of code

/*Start SQL Block*/
/*End SQL Block*/
GO 10

This will cause all the statements in this block to execute 10

This is the code from Books online that is used to generate a random number 4 times

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
      SELECT RAND() Random_Number
      SET @counter = @counter + 1

This can be replaced by

SELECT RAND() Random_Number
GO 4

Generally, while tuning a query, I would execute a block of statements or a  stored procedure multiple number of times to get a distribution of the execution times each time after clearing the cache and repeat this under varying test conditions.Whenever there is a necessity to execute statements in a loop, GO [count] syntax would come handy without having to code additional logic.

No comments:

Post a Comment