Friday, December 7, 2012

Returning row count per page

Well hi everyone, this is my first blog ever thus my first post ever. I work in IT since 2006 and have always been interested in databases since the beginning, but can also code various other languages. Enough about me...

Here is a little trick that can save you some loading time in your application. Various methods are used to have a combination of current rows on max rows shown (0-100 of 200000). By using this code, you can simplify the returns of the procedures and get all the information in one single database call. We will mainly use the OVER clause here to achieve our goal. I will update later to include the Oracle version.

TSQL


DECLARE @ProductNumber  INT
DECLARE @MaxProducts    INT
DECLARE @SearchString   VARCHAR(100)
DECLARE @MinRsltNum     INT
DECLARE @MaxRsltNum     INT

SET @ProductNumber = 1
SET @MaxProducts = 50

SET @SearchString = '2'
SET @SearchString = '%' + @SearchString + '%'
SET @MinRsltNum = 1
SET @MaxRsltNum = 5

CREATE TABLE #Products
(
      Id    INT IDENTITY(1,1),
      Name  VARCHAR(100)
)

WHILE @ProductNumber <= @MaxProducts
      BEGIN
      INSERT INTO #Products VALUES ('Product ' + CONVERT(VARCHAR, @ProductNumber))
      SET @ProductNumber = @ProductNumber + 1
      END

-- Start by reading the subquery. It will be where you do all the work.
SELECT      rslt.Name,
            rslt.RsltNum,
            rslt.RsltTotalCount,
            -- And to play a little with data!
            'Viewing ' + CONVERT(VARCHAR, MIN(rslt.RsltNum) OVER (PARTITION BY 1))
            + ' - ' + CONVERT(VARCHAR, MAX(rslt.RsltNum) OVER (PARTITION BY 1))
            + ' of ' + CONVERT(VARCHAR, rslt.RsltTotalCount)
FROM  (     SELECT      p.Name,
                        -- This gets the row number. The ORDER BY is important to ensure a stable result set.
                        ROW_NUMBER() OVER (ORDER BY Name) AS RsltNum,
                        -- This will return the total number of rows found before filtering the wanted row set.
                        COUNT(*) OVER (PARTITION BY 1) AS RsltTotalCount
            FROM  #Products p
            WHERE p.Name LIKE @SearchString
      ) rslt
WHERE       rslt.RsltNum BETWEEN @MinRsltNum AND @MaxRsltNum -- This filters the wanted row set.

DROP TABLE #Products