[Reprint] row_num implement Paging

Mssql2005, the latest Microsoft release, has made small-scale enhancements to tsql, and some functions are indeed very practical. For example, the row_number () function implements the function of paging big data with stored procedures. With this function, a simple statement can solve data paging in any situation, regardless of whether the field to be sorted is unique or not.
I personally feel that this function should be able to further optimize the performance. Of course, this job is not what we can do. If we don't develop ms, we won't be able.
Select * from (select row_number () over (order by id desc) as pos from [Table] Where year (birth) = 1981) as SP where POS between 1 and 5
The actual underlying working logic is not very clear. It would be a waste of resources if the full table sorting is required for each execution. Even if you have an index, you do not need to sort the entire table. Is it necessary to perform a full table operation of the row_number () function?
Assume that all indexes have the rowid of an index table, which is similar to the value produced by row_number. If you already have an index, can you directly perform efficient paging Based on the rowid of the index table? This method should be feasible. Further study is required if this function is available in mssql2005.
According to the above method, you should be able to write SQL statements by page:
Select from [Table] Where year (birth) = 1981 and row_number () between 1 and 5 order by ID DESC
When the analyzer executes this statement, for the row_number () function, first check whether there is an index match. If yes, it directly accesses the rowid in the index for conditional reading. If no matching index exists, then, the row_number () of the entire table is re-calculated based on the current sorting condition.
However, this method may increase the indexing workload, because each index reconstruction requires a full table row_number () operation. However, because the index has multiple fields and multiple directions, for example, if an index has two fields, four row_number () tables are required. If there are more index fields, it becomes very complicated. Therefore, you can save rowid for the sorting direction of some fields.

The following is a simple storage process that I wrote. I hope it will be useful to you. Compared with the previous storage pages, the performance is naturally very efficient. I personally think that using Stored Procedure calls should be more convenient in terms of performance and usage. If you think that SQL statements can be used in a direct program, it is completely OK.

Set ansi_nulls on
Set quoted_identifier on
Go

Create procedure [DBO]. [showpage]
@ Tblname varchar (255), -- table name
@ Strgetfields varchar (1000) = '*', -- the column to be returned
@ Strorder varchar (255) = '', -- Name of the sorted Field
@ Pagesize Int = 10, -- page size
@ Pageindex Int = 1, -- page number
@ Strwhere varchar (1500) = ''-- Query condition (Note: Do not add where)
As

Declare @ strsql varchar (5000)

If @ strwhere! =''
Set @ strwhere = 'where' + @ strwhere

Set @ strsql = 'select * from (select row_number () over ('+ @ strorder +') as pos, '+ @ strgetfields +' from ['+ @ tblname +'] '+ @ strwhere +') as SP where POS between '+ STR (@ PageIndex-1) * @ pagesize + 1) + 'and' + STR (@ pageindex * @ pagesize)

Exec (@ strsql)

This can only use more than 2005 of the database's traditional 2000 or let's use top X top