SQL Server's common paging stored procedure does not use cursors, faster! _mssql

Normally, an appropriate index is established on the SQL Server server for a table with a high frequency
This can greatly improve the data retrieval speed of the database itself, the method of establishing the index is not elaborate

If you need to return a large amount of data, from hundreds of lines to tens of thousands of rows, or even hundreds of thousands of rows of data
You will find that the response time is getting slower and even the response timeout error occurs
In order to solve this problem of large data volume request, we have to use paging mode.

In this context, JDBC is much more powerful, it can send the specified number of rows and SQL request to SQL Server, so that only the data back to the page, the principle of JDBC is not clear, but in practical use, the speed is very fast

If you can't use JDBC, the most common method is the stored procedure!

Before I write this paging store, I refer to a number of related articles on the web, which can be searched by keyword: SQL Server paging
Most of them take advantage of the top method in SQL and require an identity column for the data structure being retrieved, which is cumbersome if there are no identity columns or a federated primary key. And for the application of the original SQL retrieval part of the need to modify more places, a larger workload.

So, before I write this store, I need to be sure that the original SQL script is compatible to the fullest extent

After an afternoon of time, and I am a colleague (definitely a master) of the joint efforts to explore the following ideas:

1, determine the storage input parameters:
1 SQL script, this parameter receives the complete, correct SQL retrieval text, can pass the SQL script written in the original application directly to the
2 The data capacity of each page, is a page of how many data
3) Current page number
2, determine the paging mechanism:
1 execute the incoming SQL script and generate the result temporary table
2 Modify the structure of the temporary table, increase the identity column field
3 calculates the range of records within the specified page number according to the Identity column field and returns
4) Returns the total number of data bars for the client to display the paging

Based on the above ideas, write the following common paging stored procedures:

Copy Code code as follows:

[Code]
--// ============================
--//SQL Server Common paging stored procedures
--//Author:netwild
--//DATE:2010/07/22
--//email:netwild@163.com
--//qq:52100641 (NET Mowgli)
--// ============================

SET QUOTED_IDENTIFIER ON
Go
SET ANSI_NULLS on
Go


CREATE PROC Execbypage

@sqlQuery varchar (2000),--//input parameter: SQL retrieval statement or table name
@pageSize int,--//input parameters: Display the number of record bars per page
@pageIndex int--//input parameter: current page number

As

SET NOCOUNT on
SET ansi_warnings off

DECLARE @tmpTableName varchar (50)
Set @tmpTableName = ' # #TB1516_ ' + replace (CAST (NEWID () as varchar (40)), '-', ')--//generate random Temp table name

DECLARE @subIndex int
Set @subIndex = charindex (' from ', @sqlQuery)
if (@subIndex > 0)
A standard retrieval statement with from--//begin
DECLARE @sqlQuery1 varchar (2000)
DECLARE @sqlQuery2 varchar (2000)
Set @sqlQuery1 = substring (@sqlQuery, 1, @subIndex-1)
Set @sqlQuery2 = substring (@sqlQuery, @subIndex, Len (@sqlQuery))
Set @sqlQuery = @sqlQuery1 + ', IDENTITY (numeric,1,1) as ID1516 into ' + @tmpTableName + ' + @sqlQuery2
End
else--//table name without from
Begin
Set @sqlQuery = ' Select *,identity (numeric,1,1) as ID1516 into ' + @tmpTableName + ' from ' + @sqlQuery
End
EXEC (@sqlQuery)--//establishes and initializes temporary table data

DECLARE @indexStart varchar (@indexEnd), varchar (20)
Set @indexStart = Cast ((@pageIndex-1) * @pageSize +1 as varchar (20))--//Data Start Row ID
Set @indexEnd = Cast (@pageIndex * @pageSize as varchar (20))--//Data end Row ID

EXEC (' select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' + ' + @indexEnd)--//retrieve the page data

EXEC (' Select MAX (ID1516) as RecordCount from ' + @tmpTableName)--//Extract total number of bars

EXEC (' drop table ' + @tmpTableName)--//Delete temporary table


Go
SET QUOTED_IDENTIFIER OFF
Go
SET ANSI_NULLS on
Go

[/code]