MYBLOG

近期对SQL Server效率问题研究的比较多，近期研究了几个分页算法，在这里分享两个效率挺好的，已经封装成了存储过程，喜欢的拿去用好了！

1、分页一

USE [BLOG.lcyuandaima.com]

GO

/****** OBJECT:  STOREDPROCEDURE [DBO].[MYPAGER]    SCRIPT DATE: 2016/2/3 15:32:09 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [DBO].[Mypager] @TABLE        NVARCHAR(MAX),--要分页的表  
                                 @SELECTFIELDS NVARCHAR (MAX),--选择的字段
                                 @SELECTWHERE  NVARCHAR(MAX),--约束条件  
                                 @ORDERKEY     NVARCHAR(MAX),--排序的依据字段  
                                 @PAGESIZE     INT=15,--每页显示数目    
                                 @NOWPAGE      INT=1,--当前页码 
                                 @TOTALCOUNT   INT=0 OUTPUT --查询总数  
AS
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX) --执行分页语句1  
    DECLARE @SQLCOUNT NVARCHAR(MAX) --查询总数语句  

  BEGIN
      --查询总数  
      SET @SQLCOUNT ='SELECT @TOTALCOUNT=COUNT(0) FROM '
                     + @TABLE + ' WHERE ' + @SELECTWHERE

      EXECUTE Sp_executesql
        @SQLCOUNT,
        N'@TOTALCOUNT INT OUTPUT',
        @TOTALCOUNT OUTPUT

      SET @SQL='SELECT TOP ' + CONVERT(NVARCHAR, @PAGESIZE)
               + ' * FROM  (SELECT TOP '
               + CONVERT(NVARCHAR, @NOWPAGE*@PAGESIZE)
               + ' ROW_NUMBER() OVER (ORDER BY ' + @ORDERKEY
               + ') AS ROW,' + @SELECTFIELDS + ' FROM ' + @TABLE
               + ' WHERE ' + @SELECTWHERE
               + ') AS INFO WHERE ROW>'
               + CONVERT(NVARCHAR, (@NOWPAGE-1)*@PAGESIZE)

      EXECUTE(@SQL)
  END

2、分页二

USE [BLOG.lcyuandaima.com]

GO

/****** OBJECT:  STOREDPROCEDURE [DBO].[MYNEWPAGER]    SCRIPT DATE: 2016/2/3 15:32:06 ******/
SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [DBO].[Mynewpager] @TABLE        NVARCHAR(MAX),--要分页的表  
                                    @SELECTFIELDS NVARCHAR (MAX),--选择的字段
                                    @SELECTWHERE  NVARCHAR(MAX),--约束条件  
                                    @ORDERKEY     NVARCHAR(MAX),--排序的依据字段 
                                    @PRIMARYKEY   NVARCHAR(MAX),--主键   
                                    @PAGESIZE     INT=15,--每页显示数目    
                                    @NOWPAGE      INT=1,--当前页码 
                                    @TOTALCOUNT   INT=0 OUTPUT --查询总数  
AS
    SET NOCOUNT ON

    DECLARE @SQL NVARCHAR(MAX) --执行分页语句1  
    DECLARE @SQLCOUNT NVARCHAR(MAX) --查询总数语句  

  BEGIN
      SET @PRIMARYKEY=Substring(@PRIMARYKEY, Charindex('.', @PRIMARYKEY) + 1, Len(@PRIMARYKEY) - Charindex('.', @PRIMARYKEY))
      SET @ORDERKEY=Substring(@ORDERKEY, Charindex('.', @ORDERKEY) + 1, Len(@ORDERKEY) - Charindex('.', @ORDERKEY))
      --查询总数  
      SET @SQLCOUNT ='SELECT @TOTALCOUNT=COUNT(0) FROM '
                     + @TABLE + ' WHERE ' + @SELECTWHERE

      EXECUTE Sp_executesql
        @SQLCOUNT,
        N'@TOTALCOUNT INT OUTPUT',
        @TOTALCOUNT OUTPUT

      SET @SQL='  SELECT * FROM (SELECT  ' + @SELECTFIELDS
               + ' FROM ' + @TABLE + ' WHERE ' + @SELECTWHERE
               + ') AS TEMP1 INNER JOIN 
(
SELECT TOP '
               + CONVERT(NVARCHAR, (@NOWPAGE-1)*@PAGESIZE+@PAGESIZE)
               + ' ROW_NUMBER() OVER (ORDER BY ' + @ORDERKEY
               + ') AS ROWNUMBER,
ID FROM (SELECT  ' + @SELECTFIELDS + ' FROM '
               + @TABLE + ' WHERE ' + @SELECTWHERE
               + ') AS TEMP 
)
 AS TEMP2 ON TEMP1.' + @PRIMARYKEY + '=TEMP2.'
               + @PRIMARYKEY
               + ' WHERE TEMP2.ROWNUMBER> 0 ORDER BY TEMP2.ROWNUMBER ASC'

      EXECUTE(@SQL)
  END

实测10万条一下，两种算法相差不大，百万级千万级数据时相差比较大，分页二具有明显优势！