Do Paging in Denali
When you have a large table many times you create paging in your application allowing for faster responsiveness to the application.
Most systems don’t have some form of caching and require the database query to determine the sort order, offset where a page records should begin, and the number of records to return for the current page.
If you search Google there are many different ways to implemnt paging. None of them are easy, and some result in rapid expansion of the query cache in a 64 bit server. In the Denali release, Microsoft has implemented a syntax that is much easier to generate a page of data.
For example:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME
OFFSET 5 rows
FETCH Next 5 Rows ONLY
This beautiful query will skip the first 5 rows, and return the next 5 rows based on the sort order of TABLE_NAME ascending.
In all honesty, this isn’t the best use of your database horsepower. But when you have a large database, sometimes it is the only thing that meets your performance requirements.
Have you found any other little trinkets of gold? Send your comments to btaylor@sswug.org.
Cheers,
Ben