分页 性能探讨
CREATE       proc   up_GetTopicList
@PageNo   varchar(4)= '1 ',@PageSize   varchar(4)= '20 '
as
/*定义局部变量*/
declare   @a_intPageNo   int
declare   @a_intPageSize   int
declare   @intBeginID   int
declare   @intEndID   int
declare   @intRecordCount   int
declare   @intPageCount   int
declare   @intRowCount   int

set   @a_intPageNo   =   cast(@PageNo   as   bigint)
set   @a_intPageSize   =   cast(@PageSize   as   int)

/*关闭计数*/
set   nocount   on
/*求该分类总数   */
select   @intRecordCount   =   count(*)   from   company
if   @intRecordCount   =   0
return   0
/*判断页数是否正确*/
if(@a_intPageNo   -1)*   @a_intPageSize   >   @intRecordCount
return   (-1)

/*求开始ID*/
set   @intRowCount   =   (@a_intPageNo   -1)*@a_intPageSize   +1
/*限制条数*/
set   rowcount   @intRowCount
select   @intBeginID   =   companyID   from   company   order   by   companyID   desc

/*结束ID*/
set   @intRowcount   =   @a_intPageNo   *   @a_intPageSize
/*限制条数*/
set   rowcount   @intRowcount
select   @intEndID   =   companyID   from   company   order   by   companyID   desc

/*恢复系统变量*/
set   rowcount   0  
set   nocount   off

select   companyID,companyName,remark,editDate   from   company   where   companyID   between   @intEndID   and   @intBeginID   order   by   companyID   desc
return   (@intRecordCount)
GO  
=====================================================
strSql   =   "select   Top   "   +   pager.PageSize   +   "   a.*,b.ClassName,c.TypeName,c.ModuleName,(select   count(Id)   from   Comment   where   InfoId=a.Id)   as   CommentCount     from   [Info]   a   "   +
                                                "   LEFT   JOIN   Category   b   ON   a.CategoryId=b.Id "   +
                                                "   LEFT   JOIN   Type   c   ON   a.TypeId=c.Id "   +
                                                "   where   a.CategoryId=@CategoryId   and   a.Id   not   in(select   Top   "   +   (pager.CurrentPageIndex   -   1)   *   pager.PageSize   +   "   Id   from   [Info]   where     CategoryId=@CategoryId   order   by   UpdateTime   desc)   order   by   UpdateTime   desc ";  
========================
CREATE   procedure   P_GetPagedOrders2000
(@startIndex   int,
@pageSize   int
)
as
set   nocount   on