Now that we are familiar with the benefit of pagination it’s time to present how to accomplish it using a Microsoft SQL Server Database.

Because of the fact that MSSQL does not have a LIMIT clause some programmers might attempt to use huge SELECT constructions using TOP and WHERE. Even if these elaborate constructions may do the work, they are time and resource consuming. The option that should be used is the ROW_NUMBER() OVER (ORDER BY field) syntax. If we put this syntax in an ordinary select it will add an additional column that will hold the row number of the record. There is only one last simple step until we reach the LIMIT equivalent. Another select syntax is needed where you condition the row number column. The final query will look like this: SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY field) AS Row, * FROM Table WHERE 1=1) A WHERE Row BETWEEN OFFSET AND OFFSET+ROW_COUNT. This query will return the same records as the top query used in MySQL.

Let’s illustrate the above in a simple example using the Bookstore that decides to go online.

I will create a simple table that holds the records regarding the books for sale:

CREATE TABLE [dbo].[Books](

[Author] [varchar](255) NOT NULL,

[Book] [varchar](255) NOT NULL,

[PublishingHouse] [varchar](255) NOT NULL,

[Price] [decimal](8, 2) NOT NULL

)

After populating the table with some records the table content is:

Now let’s test the ROW_NUMBER() OVER functionality:

SELECT

*

FROM (SELECT ROW_NUMBER() OVER (ORDER BY Author) AS Row, * FROM Books) A WHERE

Row BETWEEN 3 AND 6

ORDER BY Row

The result is:

Another functionality that you can play around with is RANK() OVER, which mostly does the same thing with the exception that if you do not provide a uniquely record identifier in the OVER brackets it will duplicate some records in the Row column.

If we query for:

SELECT

*

FROM (SELECT RANK() OVER (ORDER BY Author) AS Row, * FROM Books) A WHERE

Row BETWEEN 3 AND 6

ORDER BY Row

The result will be:

This is somehow acceptable if we consider the sense of word rank. If we want the same result as in the ROW_NUMBER case we will have to query for:

SELECT

*

FROM (SELECT RANK() OVER (ORDER BY Author, Book) AS Row, * FROM Books) A WHERE

Row BETWEEN 3 AND 6

ORDER BY Row

And the result will surely be:

 

I invite you to explore these useful functionalities of Microsoft SQL Server 2005 and also would like to give a special thanks to my colleague, Cristian Bardas, for discovering them and sharing the knowledge.