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 .
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
times.
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
BEGIN
SELECT RAND() Random_Number
SET @counter = @counter + 1
END;
GO
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.
times.
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
BEGIN
SELECT RAND() Random_Number
SET @counter = @counter + 1
END;
GO
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.
