Summary of SQL Server Paging

Most people may use TOPNOTIN when learning SQL paging. Many people may still use this paging method. But has someone found the problem? SELECTTOP10 * FROMdbo. OrdersWHEREOrderIDNOTIN (SELECTTOP20OrderIDFROMdbo. OrdersORDERBYRequiredDa

Most people may use top not in when learning SQL paging. Many people may still use this paging method. But has someone found the problem? Select top 10 * FROM dbo. Orders WHERE OrderID not in (select top 20 OrderID FROM dbo. Orders order by RequiredDa

Most people may use top not in when learning SQL paging. Many people may still use this paging method. But has someone found the problem?

Select top 10 * FROM dbo. Orders WHERE OrderID not in (select top 20 OrderID FROM dbo. Orders order by RequiredDate) order by dbo. Orders. RequiredDate

Select top 10 * FROM dbo. Orders WHERE OrderID not in (select top 30 OrderID FROM dbo. Orders order by RequiredDate) order by dbo. Orders. RequiredDate

The OrderID number is 10788. Two statements have duplicates.

This is the TOP page I tested with NORTHWND. You can also test that the results of these two statements will be repeated. Test if the orderby column is repeated, then the paging data will be repeated. Both reverse and forward appear. If anyone is interested, try the following.

Select top 10 * FROM dbo. Orders WHERE OrderID not in (select top 160 OrderID FROM dbo. Orders order by RequiredDate DESC) order by dbo. Orders. RequiredDate DESC

Select top 10 * FROM dbo. Orders WHERE OrderID not in (select top 170 OrderID FROM dbo. Orders order by RequiredDate DESC) order by dbo. Orders. RequiredDate DESC

The OrderID number is 10880. Two statements have duplicates.

Many people suspect that the statement is wrong or the data is faulty. I am using the official Microsoft northwnd library, and there should be no data problems. Is there a problem with my statement?

The above is a beginner or used to use, so we can implement another implementation in sqlserver2000. Many people say that I have changed sqlserver2005. Why should I use sqlserver2000. I think that as long as someone is using it, the problem will always be solved.

So let's continue to look at the form of SQL Server using Table variables. I think many people are using this method.

DECLARE @ PAGETEMP TABLE
(

_ ROW_NUM int identity (1, 1 ),

_ TID INT

)

Insert into @ PAGETEMP (_ TID) select top 30 OrderID FROM dbo. Orders order by RequiredDate

SELECT [@ PAGETEMP]. _ ROW_NUM, * FROM Orders, @ pagetemp where dbo. orders. orderID = [@ PAGETEMP]. _ tid and [@ PAGETEMP]. _ ROW_NUM> 20 AND [@ PAGETEMP]. _ ROW_NUM <= 30

Create a table variable with an auto-increment field (_ ROW_NUM), and insert the primary key of the corresponding paging table into the temporary table (_ TID ), in this temporary table, the primary key fields of the query table are re-sorted by auto-increment according to the query conditions. The next step is to associate the table with the query table based on the re-sorted table variable. Obtain the number of rows to be returned. The paging mode of table variables is similar to that of table variable 05. Get a temporary table with auto-incrementing serial number based on the query content, and then obtain the required number of rows.

Continue to look at another Sqlserver2000 paging method. This method can also solve the above repetition problem. However, it imposes restrictions on sorting.

Select top 10 * FROM [Orders] WHERE [Orders]. [OrderID]> (
Select max ([_ T]. [OrderID]) FROM
(Select top 20 [Orders]. [OrderID] AS [OrderID] FROM [Orders] order by [Orders]. [OrderID]) [_ T])
Order by [Orders]. [OrderID]

　　

First, find the largest number in the first 20 data records. Exclude the 20 records and find the top 10 records greater than the write records. It is easy to understand. The query table must have an auto-increment number. In addition, sorting by this number has great limitations. Then, if the reverse order is min and less than to filter data.

Select top 10 * FROM [Orders] WHERE [Orders]. [OrderID] <(
Select min ([_ T]. [OrderID]) FROM
(Select top 20 [Orders]. [OrderID] AS [OrderID] FROM [Orders] order by [Orders]. [OrderID] DESC) [_ T])
Order by [Orders]. [OrderID] DESC

Finally, let's take a look at the paging Statement of sqlserver2005, using the two keywords ROW_NUMBER OVER

WITH [_ T] (
Select top 30 *, ROW_NUMBER () OVER (order by [Orders]. OrderID) AS [_ Pos] FROM [Orders]
)
SELECT * FROM [_ T] WHERE [_ T]. [_ Pos]> 20 AND [_ T]. [_ Pos] <= 30

OVER here, you can set the sorting column. ROW_NUMBER () OVER (order by [Orders]. OrderID) AS [_ Pos] creates an auto-incrementing column for us. You only need to return the desired row number when querying. This is similar to the form of table variables. Performance has also improved a lot.

The above is my summary of paging. I mainly want to talk about the TOP NOT IN method of sqlserver2000, so we will use the following two methods for Sqlserver2000 IN the future. In the new NSun, the default page type is sqlserver2005. If you are sqlserver2000, the page type is changed to table variables.