SQL Puzzle #2 - Paging through a table with GO 10

I just wrote about using GO 10 to execute a T-SQL batch 10 times in SQL Server Management Studio. Could you use it to page through data?

The challenge: Write an ad-hoc query which queries the AdventureWorks Production.Product table 10 times using GO 10, returning the next sequential row each time. My query looks something like this:

SELECT...
(some other T-SQL here)
GO 10

Here are the first two columns, first three rows when it's executed:

Beginning execution loop
ProductID   Name                 
----------- ---------------------
1           Adjustable Race      

ProductID   Name                 
----------- ---------------------
2           Bearing Ball         

ProductID   Name                 
----------- ---------------------
3           BB Ball Bearing      
...

Extra credit: Instead of sequential results, return rows one at a time but in random order. Can you use TABLESAMPLE here?:

Beginning execution loop
ProductID   Name                       
----------- ---------------------------
375         Hex Nut 5                  

ProductID   Name                       
----------- ---------------------------
731         ML Road Frame - Red, 44    

ProductID   Name                       
----------- ---------------------------
471         Lock Washer 12             
...

Note: Yes, there's no point to this quiz other than as an exercise. There are better ways to page through data with 2005 T-SQL functions like ROW_NUMBER(). Since GO operates at the batch level, it will always execute the same script each time, which breaks most of the common paging approaches. How would you work around that?

UPDATE: Here are my solutions.

-- The main puzzle

SET NOCOUNT ON
SELECT MIN(ProductID) AS ProductID INTO #productTemp FROM Production.Product
GO

SELECT * FROM Production.Product p INNER JOIN #productTemp pt ON p.ProductID = pt.ProductID

UPDATE #productTemp SET ProductID =
(SELECT MIN(p.ProductID) FROM #productTemp pt, Production.Product p WHERE p.ProductID > pt.ProductID)
GO 10

DROP TABLE #productTemp

-- Extra credit

SELECT TOP 1 * FROM Production.Product ORDER BY NEWID()
GO 10

2 Comments

  • declare @theRow int

    set @theRow = convert(int, rand() * 1000)
    print @theRow
    select top 1 * from Production.product
    where productId < @theRow
    order by productid desc
    go 10

  • and for the first one... using a temp table...

    select 0 as theRow into #temp
    go
    declare @theRow int
    select @theRow = theRow from #temp;

    with products as (
    select row_number() over (order by productid) as row, *
    from production.product)

    select top 1 *
    from products
    where row > @theRow
    update #temp set theRow = theRow + 1
    go 10
    drop table #temp

Comments have been disabled for this content.