peter (1/10/2009)
My comments are focused on non-meaningful primary keys, which is what identity columns are. It is just a compact number (32 bit int in most cases) and contrary to popular believe cannot always be assumed to be in time sequential order (identity inserts). Nor can it be assumed the total range is without gaps for that matter (failed insert transactions or explicit deletions). Thus sorting on identity is in fact likely to be meaningless unless special conditions are met (which are very hard to guarantee).
- You can achieve "time sequential order" by creating a "buffer" table for incoming data. And then move the blocks of sorted records from that buffer table into the main table;
- Gaps in the range should not matter since you could use "TOP" clause to retreive the required number of records.
... In your example it is safer to use some date field with in index on it to work with and have the user specify a datetime range to filter on in the first place (it is unlikely the user is just blindly paging without looking for something specific)
Yes.
...It is just that you should confront/combat problems where they originate.
Yes, it would be better to reuse proven technique on this or that side (in our case - database side). But what if the technique just don't fit the app requirements.
An application can do more to make interactive (browsing in this case) more responsive and efficient. It can work with larger/multiple pages internally and only show one page to the user. Say you got a visible page size of 50 entries, you simply load 550 (11 pages) using a SQL paging mechanism (which is still efficient and from there you can go 5 pages back or forward without any database access.
Yes, but a user will wait for 11 pages to come even if he needs only one. In Ajax way it would be to supply a user with the requested page and then to send him and store left and right pages on the client when he is working with the requested page. On moving him to the left page later - feed his client buffer with the next left page.