SQL Server Common stored procedure paging code (with Row_number ()

In a stored procedure, if the total number of statistics records will seriously affect the efficiency of the same query conditions the total number of records must be the same, so if the first page to save the total number of records, click on the other page when the command stored
The process no longer counts the total number of records will greatly improve system performance. The test results show that each time the total number of records used Row_number () and not using the row_number () difference is not too large, if only the first statistical total number of records used Row_number () will
is much more efficient than not using row_number ().

Structure:
Copy code code as follows:

CREATE TABLE [dbo]. [Xtest] (
[ID] [bigint] IDENTITY (1,1) not NULL,
[XName] [nvarchar] (m) NULL,
[Xdemo] [nvarchar] () NULL,
CONSTRAINT [pk_xtest] PRIMARY KEY CLUSTERED
(
[ID] ASC
With (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]
) on [PRIMARY]

Insert 500,000 data:
Copy code code as follows:

DECLARE @i int
Set @i=1
while (@i<500001)
Begin
INSERT INTO dbo. Xtest (Xname,xdemo) VALUES (CAST (@i as varchar) + ' name ' +cast (@i as varchar), ' This is the content of the +cast as @i ' + ')
Set @i=@i+1

End

In addition, an index is established for XName.
The stored procedure is as follows:
Copy code code as follows:
-- =============================================
--Author: <jiangrod>
--Create Date: <2010-03-03>
--Description: <sql2000 General paging Stored procedure > Calling Method: sp_pager2000 ' xtest ', ' * ', ' id ', ' ORDER by ID ASC ', ' xname like ' '%222name% ' ' , 3,20,0,0
-- =============================================

ALTER PROCEDURE [dbo]. [sp_pager2000]
@tblName varchar (255),--table name such as: ' Xtest '
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned: ' Xname,xdemo '
@pkName nvarchar = ' ID ',--primary Key name
@strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC '
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% '
@pageIndex int = 1,--page number: 2
@pageSize int = 20, number of records per page: 20
@recordCount int=0 out,--Total records
@doCount bit=0-not 0 statistics, 0 is not statistical (statistics can affect efficiency)
As
Declare @sql nvarchar (4000)
Declare @sqltemp nvarchar (1000)
Set @sql = ' from ' + @tblName
if (@strWhere!= ') Set @sql = @sql + ' Where ' + @strWhere
declare @strCount nvarchar (1000)
--Total Record bar number
if (@doCount!=0)
Begin
if (@strWhere!= ')
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ' where ' + @strWhere + ') '
End
Else
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ') '
End
EXECUTE sp_executesql @strCount, N ' @num INT output ', @RecordCount output
End
If @strWhere!= '
Begin
Set @strWhere = ' where ' + @strWhere
End
if (@PageIndex >1)
Begin
Set @sqltemp = @PKName + ' not in (Select top ' +cast (@PageSize * (@PageIndex-1) as nvarchar) + ' + @PKName + ' + @sql
if (@strOrder!= ') Set @sqltemp = @sqltemp + ' + @strOrder
Set @sqltemp = @sqltemp + ') '
Set @sql = ' Select top ' +cast (@PageSize as nvarchar) + ' + @strGetFields + ' + @sql
if (@strWhere!= ') Set @sql = @sql + ' and ' + @sqltemp
else Set @sql = @sql + ' Where ' + @sqltemp
End
Else
Begin
Set @sql = ' Select top ' +cast (@PageSize as nvarchar) + ' + @strGetFields + ' + @sql
End
if (@strOrder!= ') Set @sql = @sql + ' + @strOrder
EXEC (@sql)

-- =============================================
--Author: <jiangrod>
--Create Date: <2010-03-03>
--Description: <sql2005 and subsequent versions common paging stored procedures > Invocation methods: sp_pager2005 ' xtest ', ' * ', ' ORDER by ID ASC ', ' xname like ' '%222name% ' ' , 2,20,0,0
-- =============================================
ALTER PROCEDURE [dbo]. [sp_pager2005]
@tblName varchar (255),--table name such as: ' Xtest '
@strGetFields varchar (1000) = ' * ',--the column that needs to be returned: ' Xname,xdemo '
@strOrder varchar (255) = ',--sorted field name such as: ' ORDER by id DESC '
@strWhere varchar (1500) = ',--Query criteria (note: Do not add where) such as: ' XName like '%222name% '
@pageIndex int = 1,--page number: 2
@pageSize int = 20, number of records per page: 20
@recordCount int output,--Total number of records
@doCount bit=0-not 0 statistics, 0 is not statistical (statistics can affect efficiency)
As
DECLARE @strSQL varchar (5000)
declare @strCount nvarchar (1000)
--Total Record bar number
if (@doCount!=0)
Begin
if (@strWhere!= ')
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ' where ' + @strWhere + ') '
End
Else
Begin
Set @strCount = ' Set @num = (select count (1) from ' + @tblName + ') '
End
EXECUTE sp_executesql @strCount, N ' @num INT output ', @RecordCount output
End
If @strWhere!= '
Begin
Set @strWhere = ' where ' + @strWhere
End
Set @strSQL = ' SELECT * FROM (select Row_number () over (' + @strOrder + ') as ROWID, '
Set @strSQL = @strSQL + @strGetFields + ' from [' + @tblName + '] ' + @strWhere
Set @strSQL = @strSQL + ') as SP WHERE ROWID BETWEEN ' +str ((@PageIndex-1) * @PageSize + 1)
Set @strSQL = @strSQL + ' and ' +str (@PageIndex * @PageSize)
EXEC (@strSQL)

Test the above two stored procedures, test environment: Core 2 Dual-core 2.20gh,1g memory, SQL SERVER2008
to 500,000 data for paging query test
Query conditions are: XName like '%222name% '
Page1:2 seconds 937 milliseconds ": Indicates the time that the first page was used, and then the
do not use Row_number () to count every page:
Page1:2 second 937 milliseconds
Page2:3 seconds 140 milliseconds
Page3:3 seconds 453 milliseconds
Page4:3 609 milliseconds
page5:3 seconds 843 milliseconds
Page6:4 seconds 156 milliseconds
do not use Row_number () only first page statistics:
Page1:2 sec 937 Ms
page2:0 second 343 millisecond
page3:0 second 593 millisecond
page4:0 sec 812 Millisecond
page5:1 sec 46 Millisecond
Page6:1 sec 281 ms
Use Row_number () every page counts:
Page1:2 sec 937 millisecond
Page2:3 second 31 millisecond
Page3:3 sec 156 Millisecond
Page4:3 sec 296 Millisecond
Page5:3 sec 421 Millisecond
Page6:3 sec 515 ms
Use Row_nu Mber () first page statistics:
Page1:2 sec 937 ms
page2:0 second 218 Millisecond
page3:0 sec 359 Millisecond
page4:0 sec 468 Millisecond
page5:0 sec 578 Ms
page6:0 sec 687 milliseconds
Conclusion: