测试环境
硬件：CPU 酷睿双核T5750 内存：2G
软件:Windows server 2003 + sql server 2005
OK,我们首先创建一数据库：data_Test,并在此数据库中创建一表：tb_TestTable
| 01 create database data_Test --创建数据库 02 data_Test 03 GO 04 use data_Test 05 GO 06 create table tb_TestTable --创建表 07 (id int identity(1,1) primary key, 08 userName nvarchar(20) not null, 09 userPWD nvarchar(20) not null, 10 userEmail nvarchar(40) null) 11 GO |
然后我们在数据表中插入2000000条数据:
| 01 --插入数据 02 set identity_insert tb_TestTable on 03 declare @count int 04 set @count=1 05 while @count<=2000000 06 begin 07 insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn') 08 set @count=@count+1 09 end 10 set identity_insert tb_TestTable off |
我首先写了五个常用存储过程：
1，利用select top 和select not in进行分页，具体代码如下：
| 01 create procedure proc_paged_with_notin --利用select top and select not in 02 ( 03 @pageIndex int, --页索引 04 @pageSize int --每页记录数 05 ) 06 as 07 begin 08 set nocount on; 09 declare @timediff datetime --耗时 10 declare @sql nvarchar(500) 11 select @timediff=Getdate() 12 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' 13 execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql 14 select datediff(ms,@timediff,GetDate()) as 耗时 15 set nocount off; 16 end |
2，利用select top 和 select max(列键)
| 01 create procedure proc_paged_with_selectMax --利用select top and select max(列) 02 ( 03 @pageIndex int, --页索引 04 @pageSize int --页记录数 05 ) 06 as 07 begin 08 set nocount on; 09 declare @timediff datetime 10 declare @sql nvarchar(500) 11 select @timediff=Getdate() 12 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' 13 execute(@sql) 14 select datediff(ms,@timediff,GetDate()) as 耗时 15 set nocount off; 16 end |
