Paging comparison of SQL Server Data

Only part of the page by UI

If the data volume of a query result is huge, the page is displayed when the data query is required, and only one page is returned each time.

Method 1:
The idea is to extract two parts of data based on the page number, for example, extract the first 90, then extract the first 100, and then compare the difference set of the two results.

In the case of 0.3 million records, it takes about one minute and a half if only 100 pages are divided (10000 records are returned. If the index is created, it takes about 1 minute.

// Select * From // The statement cannot be modified because it is read from the result and must be used *
// (Select top @ h_count (@ filedlist) from @ tablename...) as big // retrieve records that meet the upper limit
// Where
// Big. guid // This is the key. duplicate records are filtered out from the lower limit results based on the primary key (only different data is left, that is, intersection)
// Not in
// (Select top @ l_count guid from @ table...) // lower limit
// Order @ orderby // The original format. Only the values after orderby are retained here. The values after the conditions should be retained, including gruopby and others.

The function is similar to this:
Public String makesqlpager (string sourcesql, int pageindex)
{
// Use the default page size
String orderbystr = sourcesql. substring (sourcesql. tolower (). indexof ("order "));
Int Index = sourcesql. tolower (). indexof ("select ");
String bigres = "(" + sourcesql. insert (index + 6, "TOP" + (pageindex + 1) * _ pagesize ). tostring () + "") + ") as big ";
String smallres = "(" + sourcesql. insert (index + 6, "TOP" + (pageindex * _ pagesize). tostring () + "") + ")";
Return "select * from" + bigres + "where big. guid not in" + smallres + "" + orderbystr;
}

This method can also be improved by filtering from the first result when Filtering for the second time.

Method 2:
Beginning and end,ProgramNot yet written
Select * from
(
Select Top 100 * from
(
Select top 100000 * From pagetest order by regt ASC
) As
Order by regt DESC
) As B
Order by regt ASC

Tested. It took about 29 seconds.

Comparison:
The efficiency of the first method is very low, because it requires loop comparison for multiple times, and the time complexity is a level higher. For example, the response time of this method is closely related to the obtained page number.
The second method is acceptable. It has nothing to do with the page number, but it also needs to be compared twice.

The comparison ID method is available on the Internet, but not all tables have IDs. Even if there are IDs, they are not necessarily int type.
I have not tested the efficiency of creating temporary tables using stored procedures.

SQL Server does not have the rownum function (the latest version of 2005beta2 is said to be available in Oracle), so one comparison can be performed on multiple pages.AlgorithmIt's really hard to write