create procedure sp_vlist @cpbmb varchar(6),@crbh varchar(2) with encryption as
set nocount on
declare @sqlcmd varchar(148)
if object_id('tempdb..##vlist') is not null
drop table ##vlist
set @sqlcmd='select identity(int, 1,1) as id, * into ##vlist from '+@cpbmb+' where crbh='''+@crbh+''' order by cpxh
create index ##vlist_id on ##vlist(id)'
exec(@sqlcmd)
set nocount off
例如:select * from ##vlist where id between 10 and 20 order by cpxh
这个存储过程我认为还是非常有效的,它可以针对不具有连续id的子查询进行分页,我用来开发VC的虚拟列表,现在只要内存足够,那么对于2,000,000记录的表操作起来和查询分析器一样快,当然看通的朋友知道这只是内存的问题了,即使100,000,000记录,分页速度也是一样的!