Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about what the SQL Server database pagination is. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Demand

Here, take the sample database NorthWind of SQLServer as an example, in which there is a Product table. Now suppose our requirements are to be sorted in descending order of UnitPrice and paginated with 10 records per page. The server is required to page. The parameters are the number of records per page and the page number.

Realize

Top paging

At that time, the most direct approach is to use two Top to achieve, the final return of the result is in ascending order, and then deal with it in the C # code. Here as a demonstration, use * in the statement for convenience, replace it with a specific column name in the actual development.

Paging using the ROW_NUMBER function

In fact, there is also the simplest and most direct way of thinking, that is, to use a temporary table, that is, to create a table variable in memory, which contains a self-incrementing column, a table key column, and then insert the table to be sorted into this table according to the sorting conditions and rules, and then you can use the self-incrementing column as a row number, which can only be done in earlier times, such as SQLServer2000, but for recordsets with large amounts of data. The temporary tables that need to be created are also relatively large and inefficient, so I won't introduce them here.

Paging using the OFFSETFETCH clause

Now that LINQ has done the paging so easily, is there any similar simple statement in SQLServer that can do the paging? the answer is yes, it is the OFFSETFETCH clause introduced in SQLServerCompact4.0.

Is it very similar to LINQ? OFFSEET is equivalent to SKIP,FETCHNEXT and TAKE.

The above is what the SQL Server database pagination is shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.