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