Native paging on Sql Server
-
Hi All, Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables. All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first. Is there any better solution for this? Thanks in advance. Regards, Thomas
-
Hi All, Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables. All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first. Is there any better solution for this? Thanks in advance. Regards, Thomas
SSEAR wrote:
Pagination is a basic requirement to fetch data from large tables.
No it isn't. It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
SSEAR wrote:
Is there any better solution for this?
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that. If you are still left with pages then you must first start by defining what happens when user A goes to lunch and while gone user B adds something new into the list that user A was using. (Vast majority of time it doesn't matter but the business users must decide that.) After that then you research the specific database to find out how to return a fixed size list out of a larger query. This is a 'page'. It involves determining how to select the next 'page'.
-
Hi All, Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables. All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first. Is there any better solution for this? Thanks in advance. Regards, Thomas
As jschell pointed out paging is a purely presentation layer issue, databases deal with data not pages! Having said that there are a number of hacks to get paging from the database, CTE is one you are familiar with there is also row_number() and top. These are all hacks to meet requests such as your, be thankfull you have some work around!
Never underestimate the power of human stupidity RAH
-
SSEAR wrote:
Pagination is a basic requirement to fetch data from large tables.
No it isn't. It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
SSEAR wrote:
Is there any better solution for this?
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that. If you are still left with pages then you must first start by defining what happens when user A goes to lunch and while gone user B adds something new into the list that user A was using. (Vast majority of time it doesn't matter but the business users must decide that.) After that then you research the specific database to find out how to return a fixed size list out of a larger query. This is a 'page'. It involves determining how to select the next 'page'.
jschell wrote:
It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
I agree. I was just worrying about the overkill of fetching entrie table. Think about TOP{no of records}. Can the pagination implement like this? Oky, I am not ignoring the additional overhead to calculate the 'current page' on Sql Server.
jschell wrote:
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
This is not pratical in many situations. Consider a shopping web site which have a statergy is to force the customer to buy products even though they really dont require it. The customer may doesnt have idea what they want and like to surf through available items.
-
As jschell pointed out paging is a purely presentation layer issue, databases deal with data not pages! Having said that there are a number of hacks to get paging from the database, CTE is one you are familiar with there is also row_number() and top. These are all hacks to meet requests such as your, be thankfull you have some work around!
Never underestimate the power of human stupidity RAH
-
jschell wrote:
It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
I agree. I was just worrying about the overkill of fetching entrie table. Think about TOP{no of records}. Can the pagination implement like this? Oky, I am not ignoring the additional overhead to calculate the 'current page' on Sql Server.
jschell wrote:
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
This is not pratical in many situations. Consider a shopping web site which have a statergy is to force the customer to buy products even though they really dont require it. The customer may doesnt have idea what they want and like to surf through available items.
SSEAR wrote:
This is not pratical in many situations.
Actually it is practical in most real situations.
SSEAR wrote:
Consider a shopping web site which have a statergy is to force the customer to buy products even though they really dont require it. The customer may doesnt have idea what they want and like to surf through available items.
And is that exactly what you are implementing? From those same sites - do they allow the customers an option to pick out which customer they are from a list of all customers?
-
Hi All, Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables. All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first. Is there any better solution for this? Thanks in advance. Regards, Thomas
Hi, Try below If you want page index 1 (between 10-9 and 10) page index 2 (between 20-9 and 20) . . page index 25 (between 250-9 and 250) SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS [PageIndex],* FROM sys.objects ) AS TB1 WHERE PageIndex BETWEEN 10-9 AND 10