pagination - Microsoft SQL Server Paging -

There are many SQL server paging questions on stack overflow and many of them talk about the use of ROW_NUMBER () over (ORDER BY ...) and CTE . Once you come in hundreds of rows and start sorting the non-primary key values ​​and custom where the stories are added, then this method becomes very uncontrollable. I have a dataset of several million rows, which I have custom Trying to page through sorting and filtering, but I'm getting poor performance, even on all those areas, With the method, which I'll sort through and extend filter. I even went to my indexed columns to include each indexed column, but it was very helpful and seriously bloated in my database.

I saw that Stackworflow paging takes only 500 million seconds, no matter what the sorting criteria is or what you click on the page, anyone know that paging is SQL Server 2008 How to work efficiently with millions of lines? This would involve getting the total ranks possible.

This stack overflow question about paging in my current query is:

Anyone know how to do paging work efficiently with millions of rows in SQL Server 2008?

If you want accurate right paging, there is no substitute for building index (position line number) for each record. However, there are options.

(1) Number of pages (records)

• You can use an approximation from sysindexes.rows (
• You can use the trigger for paging second, table row number

  (2) to keep you perfectly accurate.

  ( A)
  You can show jumps inside the page, the next five pages are said to be on both sides of a record, they have to scan at most [page size} x 5 on each side. If your underlying query lends itself to travel on a sequential order, then it should not be slow. So a record has been given, you can go to the previous page (sorting the order, a asc, b desc Select

    top (@pexsees) from T. Tbl x to inner tbl t (ta = xa and tb> xb) or (ta & lt; ax) Where x.id = @X order by ascend, tb desc   
   (the last {page size} of earlier records of X)  
   To return five pages , You increase it from that sub-class to the top (@pisis * 5) and later the top (@pense). / P> 
   Downside: This option requires that you can not go straight to a particular place, your options are only first (simple), last (easiest), next / first, <5 page side side  
   (B) 
   If paging is always quite specific and guessing, then maintain an INDEXED view or trigger-up-to-date table that does not have a span in the row number. This can be an option if the table is only seen in an end on the only end of the spectrum, which is filled with the gap from the vacuum, rather than easily transferring too many records.  
   This approach gives you a line line (last line) and gives direct access to any page.