Rotating Header Image

Turn the Page

Something that has been problematic at best for developers to deal with historically is the idea of “paging”. Basically being able to say that if I was assuming 10 rows per page, I want to see what the contents of page 250 would be.

In order for you to work with my example (which uses a tweaked version of the “10,000 Random Names Database” you’ll need to download the following file (and be working with CTP3 of SQL2012) πŸ™‚ :

10000 Random Names File

Right Click and save it in a location that you will reference in the following SQL Script:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblNames]') AND type in (N'U'))
DROP TABLE [dbo].[tblNames]
GO
CREATE TABLE tblNames
(
	NameID		int PRIMARY KEY,
	FirstName	nvarchar(100),
	LastName	nvarchar(100)
)
BULK INSERT tblNames
FROM	'C:\BLOG\10000RandomNames.csv'
WITH
(
	FIELDTERMINATOR = ',',
	ROWTERMINATOR = '\n'
)
GO
CREATE INDEX idx_tblNames_LastName ON tblNames(LastName)
----------------------------------------------------------
----------------------------------------------------------
DECLARE	@RowsPerPage INT = 10
DECLARE	@PageRequest INT = 250
 
SELECT	NameID, FirstName, LastName
FROM	dbo.tblNames
ORDER BY LastName
OFFSET (@RowsPerPage * (@PageRequest - 1)) ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

So let breakdown what we’ve done here:

  • Created our working table
  • Populated with the contents of the 10000 Random Names File
  • Created an index on LastName (not required by the DBA in me thought it was necessary πŸ˜‰ )
  • Declared 2 variables
    • @RowsPerPage – This is the number of rows contained on a page
    • @PageRequest – This is the page that I’m looking to retrieve
  • Ran a SELECT statement utilizing the new “OFFSET” and “FETCH” parameters to return me @RowsPerPage (10 rows) of data starting at offset @PageRequest (page 250)

How cool is that?

Books Online Reference: ORDER BY Clause: Paging

If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

2 Comments

  1. Earlier this year, HTC released the One M9, a mighty fine flagship smartphone which featured surface-level changes to a winning formula established by the HTC One M8.

  2. If you do upgrade, consider the new jet black model. The back is polished so finely that it looks like glass, yet has the strength of aluminum.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.