SQL two simple paging query methods, SQL paging Query Method

SQL two simple paging query methods, SQL paging Query Method

In the past, we may have used the skip and take method of linq for paging queries, but few of them write SQL paging queries by themselves, because most of the time, we are calling others' methods.

I recently saw a document. I feel that when the paging query implemented in the method is called into the database, the actual underlying calling is still SQL paging query. For example, we use linq to write a paging query, after converting to an SQL expression, we found that:

In actual calling, we found that the underlying SQL layer is paging in this way.

Next, this article describes two types of SQL paging queries.

I. TOP Mode

---- The first paging query method: TOP method declare @ page int = 3 -- current page declare @ nums int = 5 -- five select top (@ nums) [Ar_id] entries per page, [Ar_Title] from [ta_Article] where [Ar_id] not in (select top (@ nums) * (@ page-1 )) [Ar_id] from [ta_Article] order by [Ar_id]) order by [Ar_id]

Top is the number of rows before selection. When top pages are used, for example, we need to take 3rd pages and 5 data entries per page. At this time, we can first remove the data from the first two pages, use top to select the first five data entries.

2. ROW_NUMBER () method

This method is supported only from SQL SERVER 2008. Does it feel like it is similar to Oracle ~

 

-- Second paging query method: ROW_NUMBER () method declare @ pageNum int = 3 -- current page declare @ EachNums int = 5 -- five select * from (select [Ar_id], [Ar_Title], ROW_NUMBER () over (order by [Ar_id], [Ar_Title]) as row number from [ta_Article]) as t where t. row number between (@ EachNums) * (@ pageNum-1) + 1) and (@ pageNum) * (@ EachNums)

Row_Number is used to paging the line number, and the last between is used to determine the line number.

Summary:

There are many SQL paging methods. When selecting how to paging, you should consider whether the previous page or the next page is commonly used. If it is a relatively front page, the top mode is good. If it is back, you can select rownumber. In addition, you need to consider it comprehensively. In comprehensive consideration, the rownumber method is common and efficient.