Painful SQL Server Pagination
When I used MySQL back in 2000, I took for granted the ability to paginate result sets in the server with the simple LIMIT clause. (Other open source databases, like Firebird and PostgreSQL, have similar constructs.) Querying for a page of the result set is as simple as this: # Fetches rows 21-30 # 20 is an offset, 10 is the number of records to fetch select * from Users limit 20, 10; You should only return from the server the actual records that the application will use, and that normally means a small chunk of records at a time. To me, the ability to easily page through records is a fundamental requirement of a database server. I had a big surprise when I first used SQL Server 2000, because it didn't have a standard way of paginating records. To paginate on the server, there were all kinds of convoluted nested queries or costly temporary tables. These operations were expensive for the database to process and difficult for the developers to create and maintain. Assumin...