Paging records in MS SQL Server 2005

After years of torturing their SQL Server users, Microsoft provided a “better” way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about ROW_NUMBER (Transact-SQL).

It is simply unacceptable, non-standard, impractical! By the way, you can not use column numbers (e.g. ORDER BY 1, 2 DESC); you must specify column names.

As usual, Microsoft programmers do not “think” and do not expect to work on millions of records, they simply say: “Buy more RAM, faster CPU, a better machine!” etc. Upgrade, upgrade, upgrade! They still do not understand that they will not be able to replace international standards organisation or something like that.

Unfortunately, we can not force our users not to ask for all the records; if they want to see them, we need to provide the facility, and show the records page by page.

Possibly, MySQL developers thought about the speed of the database engine while they were developing it, and LIMIT clause was a part of it since middle ages!

Similarly, Firebird SQL has a very simple clause to get a page of records like this, using FIRST m SKIP n syntax:

PostgreSQL works same as MySQL:

Although Oracle SQL has a function (similar to MS SQL Server 2005) called ROW_NUMBER(), they also offer a pseudocolumn called ROWNUM to achieve the same:

Note that this method requires modification of the criteria (WHERE clause) of the original query. I do not want to comment on who is mimicing who, but it is known that Microsoft do it more often, or simply buy the rival company owning a popular software application.

You can easily get lost inside the terrible documentation of IBM for DB2 database: it supports the approach using ROW_NUMBER() function.

My favourite database management systems are: Firebird, MySQL and Oracle. Sometimes you are not given any options and have to use what is available on the application server, like MS SQL Server 2000/2005 on a Windows server, MySQL 5 usually on a Linux machine.

Author: murat

Leave a Reply