Five paging stored procedures based on SQL Server and Performance Comparison

Source: Internet
Author: User

Five paging stored procedures based on SQL Server and Performance Comparison

In SQL Server database operations, we often use stored procedures to implement paging processing of the queried data to facilitate browsing by viewers.

Create a database data_Test:

Create database data_Test GO use data_Test GO create table tb_TestTable -- create a table (id int identity (1, 1) primary key, userName nvarchar (20) not null, userPWD nvarchar (20) not null, userEmail nvarchar (40) null) GO

Insert data:

set identity_insert tb_TestTable on  declare @count int  set@count=1  while @count<=2000000  begin  insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn')  set @count=@count+1  end  set identity_insert tb_TestTable off 

1. Use select top and select not in for paging

The Code is as follows:

Create procedure proc_paged_with_notin -- use select top and select not in (@ pageIndex int, -- page index @ pageSize int -- number of records per page) as begin set nocount on; declare @ timediff datetime -- time consumed declare @ SQL nvarchar (500) select @ timediff = Getdate () set @ SQL = 'select top '+ str (@ pageSize) + '* from tb_TestTable where (ID not in (select top' + str (@ pageSize * @ pageIndex) + 'id from tb_TestTable order by id asc )) order by ID 'execute (@ SQL) -- because the select top is not directly connected to the parameter, it is written as a string @ SQL select datediff (MS, @ timediff, GetDate ()) set nocount off; end

2. Use select top and select max (column key)

Create procedure proc_paged_with_selectMax -- use select top and select max (column) (@ pageIndex int, -- page index @ pageSize int -- number of page records) as begin set nocount on; declare @ timediff datetime declare @ SQL nvarchar (500) select @ timediff = Getdate () set @ SQL = 'select top '+ str (@ pageSize) + '* From tb_TestTable where (ID> (select max (id) From (select top' + str (@ pageSize * @ pageIndex) + 'id From tb_TestTable order by id) as TempTable) order by id' execute (@ SQL) select datediff (MS, @ timediff, GetDate () as time-consuming set nocount off; end

3. Use select top and intermediate Variables

Create procedure proc_paged_with_Midvar -- use the ID> maximum ID value and the intermediate variable (@ pageIndex int, @ pageSize int) as declare @ count int declare @ ID int declare @ timediff datetime declare @ SQL nvarchar (500) begin set nocount on; select @ count = 0, @ ID = 0, @ timediff = getdate () select @ count = @ count + 1, @ ID = case when @ count <= @ pageSize * @ pageIndex then ID else @ ID end from tb_testTable order by id set @ SQL = 'select top '+ str (@ pageSize) + '* from tb_testTable where ID>' + str (@ ID) execute (@ SQL) select datediff (MS, @ timediff, getdate () as time-consuming set nocount off; end

4. Use Row_number () to add an index to the data row in SQL server 2005.

Create procedure proc_paged_with_Rownumber -- use Row_number () (@ pageIndex int, @ pageSize int) in SQL 2005 as declare @ timediff datetime begin set nocount on; select @ timediff = getdate () select * from (select *, Row_number () over (order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank> @ pageSize * @ pageIndex and IDRank <@ pageSize * (@ pageIndex + 1) select datediff (MS, @ timediff, getdate () as time consuming set nocount off; end

5. Use temporary tables and Row_number

Create procedure proc_CTE -- use temporary tables and Row_number (@ pageIndex int, -- page index @ pageSize int -- number of page records) as set nocount on; declare @ ctestr nvarchar () declare @ strSql nvarchar () declare @ datediff datetime begin select @ datediff = GetDate () set @ ctestr = 'with Table_CTE as (select ceiling (Row_number () over (order by id asc)/'+ str (@ pageSize) +') as page_num, * from tb_TestTable )'; set @ strSql = @ ctestr + 'select * From Table_CTE where page_num = '+ str (@ pageIndex) end begin execute sp_executesql @ strSql select datediff (MS, @ datediff, GetDate ()) set nocount off; end

Among the above five methods, the third method using select top and intermediate variables is the most efficient. This is the end of all the five methods for SQL Server paging stored procedures and performance comparison. I hope this will help you.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.