database - Is it possible to do data paging in SQL Server (2012) in constant time? -

I am looking for a solution to such functionality for which I am responsible, and I think the least part Due to query performance of the database, we use stored procedures to ask "page" of data in a pretty standard way.

Viewing this simple table populated with sample data:

  Insert table data (value unique scholarly zero, interrupt PK_Data primary key package (value)) data - SeedTable is ~ 2M rows SeedTable   
 Select newid () and return this stored data to this stored procedure: (This requires explicitly Sql2012, although the use of ROW_NUMBER () Sql2008 style behaves similarly):  
  to get proc GetDataPage @offset int, @   
 Then I test the performance of this sproc with this codecode:   
 Then select the value by the offset offset offset offset value of the rows = P> 
  const int PageSize = 50; Const int maxCount = 50000; {Conn.Open (); using var conn = new SqlConnection ("data source =. Initial catalog = testdb; integrated protection = true;")); Int a = 0; For (int i = 0 ;; i + = PageSize) {use (var cmd = conn.CreateCommand ()) {cmd.CommandType = System.Data.CommandType.StoredProcedure; Cmd.CommandText = "GetDataPage"; Var OID = CMD. CretePamator (); Var offset = cmd.CreateParameter (); Offset Value = ii; Offset Parameter name = "offset"; Cmd.Parameters.Add (offset); Var calculation = CMD. CretePamator (); Count.Value = PageSize; Count.ParameterName = "Count"; Cmd.Parameters.Add (count); Var sw = stopwatch Startup (); Int c = 0; (Reader.Read ()) when using (var reader = cmd.ExecuteReader ()) {c ++; }} A + = c; Sw.Stop (); Console.light line (swipe effect + "\ t" + a); If (c & lefthinum; pansize || a> = maxcount) break; }}}   
 When I chart the output of this code, I get the following:   
 I expected that this kind of paging in SQL is consistently showing time or perhaps the logarithm in the worst, but it is clear from the chart that the performance is linear. / P> 
 Is there any special tips for improving this work?  
 Is there another way of this thing that can happen faster?  
 What other databases behave the same way?  
 
 To change the experimental code to use the "From Page" technique, that suggests Kevin Stalici, results in the following:  
   
 This impressive display is very much as I want. Now I need to know if I can apply it to my real problem. The potential problem is that it does not allow for "random access" of data, but only after reaching it like a cursor. I know that it should be seen what I am doing that violates every assumption of good database design.   
 
 
 The most obvious possibility is the app design only. Provide filter criteria to your users Users generally There are some ideas that they are looking for and instead of results will be instead of pages of 1000 pages. How often can you pass page 10 on a Google search?  
 After saying this, you can try to store the ID of the previous row (clustering index value) and use it in your SQL section. If you have permission to sort on different keys (like last name), then submit the clustered index ID value and the last last name of the previous page. Then type your SQL in this way (in case of duplicate key parameters, you need to always order your main area and cluster ID value to sort the records):  
  Select top (@country) ID, last name, data from first name, where LastName & gt; = @previousLlastName and Id> @previousId Orders LastName, Id   
 All you want to index fields that are sorted can not be sure how the above will be displayed but I hope that from the search on the indexed fields (Log n) will be displayed.  
 Another option to continue the entire list, in order, with the row value, the source data changes every time, behind the scenes, and pulls the app from the assured table.  
 Good question ... let us know how it turns, please!