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).

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

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!

SELECT SalesOrderID, OrderDate
FROM SalesOrderHeader
ORDER BY OrderDate
LIMIT 10 OFFSET 49

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

SELECT
FIRST 10 SKIP 49
SalesOrderID, OrderDate
FROM SalesOrderHeader
ORDER BY OrderDate

PostgreSQL works same as MySQL:

SELECT SalesOrderID, OrderDate
FROM SalesOrderHeader
ORDER BY OrderDate
LIMIT 10 OFFSET 49

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:

SELECT SalesOrderID, OrderDate
FROM SalesOrderHeader
WHERE ROWNUM BETWEEN 50 AND 60
ORDER BY OrderDate

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.

Tags: , , , , ,
Categories: