[SQL] Using 'GO 100' to execute a batch 100 times

The GO statement is used by SQL Server as a batch terminator. It's recognized by by tools which run scripts like SSMS, SMO, and SQLCMD, but it's not technically T-SQL. SQL Server 2005 added a new little trick to the GO command which lets you specify a number of times to execute the batch: GO 10

Neat. GO 10 reminds me of GOTO 10, the funnest command of all time. What would you use GO 10 for? Here are a few ideas:

Inserting test rows in a table

There's a nice example on MSSQLTips which shows how to use it to insert rows into a table:


CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier) 
GO 
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())  
GO 1000

Use it for a quick and dirty comparison testing

Last week I was working with Rob Conery on a query that would allow paging through view or stored procedure results for the SubSonic QuickTable control. I threw some ideas at him and he made it work: 


DECLARE @Page int
DECLARE @PageSize int 
SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 
SELECT * INTO #temp FROM Invoices 
ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp

Pretty good, I thought, but my hunch was that it would better to add an identity index before adding data. First we'll select zero rows into the temp table to get the base table schema, then we add the index, and then we insert the data, like this:


SELECT * INTO #temp FROM Invoices WHERE 1 = 0 
ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 
INSERT INTO #temp SELECT * FROM Invoices 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

The best way to figure that out was to compare the results, but both ran in less than a second so it was tough to get a good idea on a single execution. Using GO 100 after each script, I was able to see a 40% improvement and feel like it was accurate enough show the change was worth implementing. Note that I'm clearing all buffers before running each batch to try to keep the playing field even.


-- Clear buffers 

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL') 

CHECKPOINT
DBCC DROPCLEANBUFFERS
GO 

-- Script A 

DECLARE @Page int
DECLARE @PageSize int 

SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 

SELECT * INTO #temp FROM Invoices 

ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp 

GO 100

We'll run the first script (against Northwind) and note the total time, then run the second script: 


-- Clear buffers 

DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL') 

CHECKPOINT
DBCC DROPCLEANBUFFERS
GO 

-- Script B 

DECLARE @Page int
DECLARE @PageSize int 

SET @Page = 2
SET @PageSize = 10 

SET NOCOUNT ON 

SELECT * INTO #temp FROM Invoices WHERE 1 = 0 

ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1) 

INSERT INTO #temp SELECT * FROM Invoices 

SELECT * FROM #temp
WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize) 

DROP TABLE #temp
GO 100

Remember that you can't use GO 100 in stored procedures - it's not valid T-SQL. It's just a little feature in Microsoft's ad-hoc query interfaces, like SSMS.

3 Comments

  • Using SQL 2005's WITH temporary table and ROWNUMBER() functions sees further speed increase however you have to choose fields for ROWNUMBER to operate on so this example will give a different result at first however runs in 2 seconds for 100 loops.

    -- Clear buffers
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')

    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    -- Script D
    DECLARE @Page int
    DECLARE @PageSize int
    DECLARE @MaxRecord int

    SET @Page = 2
    SET @PageSize = 10
    SET @MaxRecord = @Page * @PageSize

    SET NOCOUNT ON

    WITH TempInvoice AS
    (SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID, ProductID) As RowNumber FROM Invoices)
    SELECT * FROM TempInvoice WHERE RowNumber BETWEEN ((@Page - 1) * @PageSize + 1) AND (@MaxRecord)

    GO 100

  • Not sure where my other comment went however if you're not on SQL 2005 or can't decide on a set of fields for the ROW_NUMBER function then a simple speed-up to Script B is:

    -- Clear buffers

    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')

    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO

    -- Script B

    DECLARE @Page int
    DECLARE @PageSize int

    SET @Page = 2
    SET @PageSize = 10
    Set @MaxRecord = @Page * @PageSize

    SET NOCOUNT ON

    SELECT * INTO #temp FROM Invoices WHERE 1 = 0

    ALTER TABLE #temp ADD _indexID int PRIMARY KEY IDENTITY(1,1)

    SET ROWCOUNT @MaxRecord
    INSERT INTO #temp SELECT * FROM Invoices
    SET ROWCOUNT 0

    SELECT * FROM #temp
    WHERE _indexID BETWEEN ((@Page - 1) * @PageSize + 1) AND @MaxRecord

    DROP TABLE #temp
    GO 100

  • Hi,

    this might be another option to consider...

    SELECT _indexID int IDENTITY(1,1),* INTO #temp FROM Invoices

    Cheers
    Gert

Comments have been disabled for this content.