sqlserver关于分页存储过程的优化【让数据库按我们的意思执行查询计划】

复制代码 代码如下:

--代码一DECLARE @cc INT
SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1
SET @cc = @@ROWCOUNT
SELECT n.* FROM news AS n WITH(NOLOCK), #tb As t WHERE t.RowIndex>@PageIndex*@PageSize AND t.RowIndex<=(@PageIndex+1)*@PageSize AND t.newsid=n.newsid
SELECT @cc
DROP TABLE #tb

复制代码 代码如下:

--代码二
DECLARE @cc INT
SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1
SET @cc = @@ROWCOUNT
SELECT NewsId INTO #tb2 FROM #tb As t WHERE t.RowIndex>@PageIndex*@PageSize AND t.RowIndex<=(@PageIndex+1)*@PageSize
SELECT * FROM news WITH(NOLOCK) WHERE NewsId IN (SELECT * FROM #tb2)
SELECT @cc
DROP TABLE #tb
DROP TABLE #tb2