User Tools

Site Tools


notes:sql:techniques_paging

Paging in SQL Server

Below there is a complete script presenting one of possible paging techniques in SQL Server. Copy and run the script:

-- Drop tables if they exist
IF OBJECT_ID('Book', 'U') IS NOT NULL DROP TABLE Book
GO
IF OBJECT_ID('Publisher', 'U') IS NOT NULL DROP TABLE Publisher
GO
 
-- Create Publisher table
CREATE TABLE Publisher (
	PubId CHAR(2) PRIMARY KEY,
	-- Publisher name
	PubName NVARCHAR(250) NOT NULL DEFAULT '',
) ON [PRIMARY]
GO
 
-- Create Book table
CREATE TABLE Book (
	BookId INT IDENTITY(1,1) PRIMARY KEY,
	-- Publisher of the Book
	PubId CHAR(2) NOT NULL CONSTRAINT FK_Publisher_Book 
        FOREIGN KEY REFERENCES Publisher(PubId),
	-- Full tile (together with subtitle) 
	Title NVARCHAR(250) NOT NULL,
	-- A comma-separated list of authors
	Authors NVARCHAR(250) NOT NULL,
	-- 13-digit ISBN (no spaces or separators). 
	Isbn13 CHAR(13) NOT NULL,
	-- Date of publication
	PubDate DATETIME NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX UX_Book_Isbn13 ON Book(Isbn13)
GO
 
-- Create a sproc to retrieve books
IF OBJECT_ID('GetBooks', 'P') IS NOT NULL DROP PROC GetBooks
GO
CREATE PROC GetBooks
	@PageSize INT,
	@PageNo INT,
	@SortOrder TINYINT,
	@TotalItems INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON
 
	-- Determine record boundaries
	DECLARE @StartRow INT, @EndRow INT
	SET @StartRow = (@PageNo-1) * @PageSize + 1
	SET @EndRow = (@PageNo-1) * @PageSize + @PageSize
 
	-- Count the total number of records on all pages
	SELECT @TotalItems = COUNT(*) FROM Book
 
	-- Apply paging
	SELECT BookId, Title, Authors, Isbn13, PubName
	FROM (SELECT BookId, PubName, Title, Authors, Isbn13, PubDate,
          ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
                                      CASE WHEN @SortOrder=2 THEN PubDate END ASC,
                                      CASE WHEN @SortOrder=3 THEN Title END ASC,
                                      CASE WHEN @SortOrder=4 THEN Isbn13 END ASC) AS RowNum
		  FROM Book bk JOIN Publisher pub ON bk.PubId=pub.PubId
	     ) AS BooksWithRowNum
	WHERE RowNum BETWEEN @StartRow AND @EndRow
	ORDER BY CASE WHEN @SortOrder=1 THEN PubDate END DESC, 
             CASE WHEN @SortOrder=2 THEN PubDate END ASC,
             CASE WHEN @SortOrder=3 THEN Title END ASC,
             CASE WHEN @SortOrder=4 THEN Isbn13 END ASC
 
	SET NOCOUNT OFF
END
GO
 
-- Insert sample publishers
SET NOCOUNT ON
INSERT INTO Publisher VALUES ('AW', 'Addison-Wesley')
INSERT INTO Publisher VALUES ('MK', 'Morgan Kaufmann')
INSERT INTO Publisher VALUES ('PH', 'Prentice Hall')
INSERT INTO Publisher VALUES ('OR', 'O''Reilly Media')
GO
 
-- Insert sample books
SET NOCOUNT ON
INSERT INTO Book VALUES ('AW', 'Accelerated C++: Practical Programming by Example', 
    'Andrew Koenig, Barbara E. Moo', '9780201703535', CAST('20000824' AS DATETIME))
INSERT INTO Book VALUES ('AW', 'The C++ Standard Library', 
    'Nicolai M. Josuttis', '9780201379266', CAST('19990822' AS DATETIME))
INSERT INTO Book VALUES ('AW', 'Effective C++', 
    'Scott Meyers', '9780321334879', CAST('20050522' AS DATETIME))
INSERT INTO Book VALUES ('OR', 'C++ Pocket Reference', 
    'Kyle Loudon', '9780596004965', CAST('20030601' AS DATETIME))
INSERT INTO Book VALUES ('OR', 'Practical C++ Programming', 
    'Steve Oualline', '9780596004194', CAST('20030101' AS DATETIME))
INSERT INTO Book VALUES ('AW', 'Absolute C++ (4th Edition)', 
    'Walter Savitch', '9780136083818', CAST('20090313' AS DATETIME))
INSERT INTO Book VALUES ('AW', 'C++ Templates: The Complete Guide', 
    'David Vandevoorde, Nicolai M. Josuttis', '9780201734843', CAST('20021122' AS DATETIME))
INSERT INTO Book VALUES ('OR', 'C++ the Core Language', 
    'Doug Brown, Gregory Satir', '9781565921160', CAST('19951026' AS DATETIME))
INSERT INTO Book VALUES ('AW', 'Principles and Practice Using C++', 
    'Bjarne Stroustrup', '9780321543721', CAST('20081225' AS DATETIME))
INSERT INTO Book VALUES ('OR', 'C++ in a Nutshell', 
    'Ray Lischner', '9780596002985', CAST('20030401' AS DATETIME))
GO
 
-- Verification
DECLARE @TotalItems INT, @TotalItemsOUT INT
SET @TotalItemsOUT=0
 
/*
@SortOrder: 1 - PubDate DESC
            2 - PubDate ASC
            3 - Title ASC
            4 - Isbn13 ASC
*/
 
EXEC GetBooks @PageSize=5, @PageNo=2, @SortOrder=3, @TotalItems=@TotalItemsOUT OUTPUT
PRINT @TotalItemsOUT -- prints '10'
GO

Results - the second page (five books per page) sorted by title:

BookId  Title                              Authors                    Isbn13         PubName 
------  ---------------------------------  -------------------------  -------------  --------------
8	C++ the Core Language              Doug Brown, Gregory Satir  9781565921160  O'Reilly Media
3	Effective C++                      Scott Meyers               9780321334879  Addison-Wesley
5	Practical C++ Programming          Steve Oualline             9780596004194  O'Reilly Media
9	Principles and Practice Using C++  Bjarne Stroustrup          9780321543721  Addison-Wesley
2	The C++ Standard Library           Nicolai M. Josuttis        9780201379266  Addison-Wesley
notes/sql/techniques_paging.txt · Last modified: 2015/06/24 by admin