A stored procedure to achieve data paging display example, there are more suitable for friends to follow the thread AH

Using database centralized to implement data paging sometimes you run into performance problems, because the T_sql statement is also familiar, so I want to fully exploit the potential of the database, SQL Server 2005 good use, can not let it idle ah. The foreground program wants 10 rows of data per page, and here's a simple example:--Suppose my data says this
CREATE table TB (ID int NOT NULL identity (1, 1) primary key, AA varchar (100))
Go
CREATE PROCEDURE Tb_getlist
(
@PageIndex int,--Start Page
@PageRowCount INT--Number of rows of data displayed per page
)
As
BEGIN
; With PT (Idx,rownum) as
(
Select Id,row_number () over (order by ID)
From TB
)
Select Top (@PageRowCount) t. * from TB t,pt p where t.id = P.idx and P.rownum > (@PageIndex-1) * @Page RowCount
End
Go

--If SQL Server 2000, the stored procedure can write this:
Alter procedure Tb_getlist
(
@PageIndex int,--Start Page
@PageRowCount INT--Number of rows of data displayed per page
)
As
BEGIN
Declare @pt table (Idx int, rownum int identity (1, 1))
Insert into @pt (IDX) Select IDs from TB order by Id
Select Top (@PageRowCount) t. * from TB T, @pt p where t.id = P.idx and P.rownum > (@PageIndex-1) * @p Agerowcount
End
Go

EXEC tb_getlist 1, 10--first page data
EXEC tb_getlist 2, 10--second page data
EXEC tb_getlist 3, 10--third page data

Some people will say, why not directly using the table's self-increasing paragraph to do pagination index it. Since the increment of the paragraph in the data after the deletion is not continuous, so I usually use a table's primary key to do the paging index, having them give a sequential number in a set or in a temporary table, by the order of query conditions or in the reverse sequence, to compute that data needs to be realistic, rather than getting the data out of the whole brain, Paging through the foreground data controls should be much better, think about, a table has 10 segments, where several fields hold large data, there are 100,000 rows of data, a one-time return, probably not more than 10 seconds is uncertain, probably the first time out, so this time can not expect those dbgrid, The GridView What.

There is no better way, you can tell the younger brother.