SQL数据分页技术

AdventureWorks2008

 

1、Top*Top

  1   - 使用  Top  *  Top 
  2   DECLARE   @Start   datetime , @end   datetime  ;
   3   SET   @Start  =  getdate  ();
   4  
  5   DECLARE   @PageNumber   INT ,  @PageSize   INT ,  @Sql   varchar ( max  );
   6   SET   @PageNumber  =  5000  ;
   7   SET   @Pt  =  10  ;
   8   SET   @Sql  =  ‘  SELECT T2.* FROM (
   9       SELECT TOP 10 T1.* FROM 
  10           (SELECT TOP   ‘   +   STR ( @PageNumber  *  @PageSize )  +  ‘   * FROM Production.TransactionHistoryArchive
  11           ORDER BY ReferenceOrderID ASC) AS T1
  12       ORDER BY ReferenceOrderID DESC) AS T2
  13   ORDER BY ReferenceOrderID ASC  ‘  ;
  14   EXEC  ( @sql  );
  15  
 16   SET   @end  =  getdate  ();
  17   PRINT   Datediff (millisecond, @Start , @end );

2、表变量

  1   --  使用表变量 
  2   DECLARE   @Start   datetime , @end   datetime  ;
   3   SET   @Start  =  getdate  ();
   4   DECLARE   @PageNumber   INT ,  @PageSize   INT ,  @Sql   varchar ( max  );
   5   SET   @PageNumber  =  5000  ;
   6   SET   @PageSize  =  10  ;
   7  
  8   DECLARE   @local_variable   table  (RowNumber  int   identity ( 1 , 1 ), [  TransactionID  ]   [  int  ]  ,
   9       [  ProductID  ]   [  int  ]  ,
  10       [  ReferenceOrderID  ]   [  int  ]  ,
  11       [  ReferenceOrderLineID  ]   [  int  ]  ,
  12       [  TransactionDate  ]   [  datetime  ]  ,
  13       [  TransactionType  ]   [  nchar  ] ( 1  ),
  14       [  Quantity  ]   [  int  ]  ,
  15       [  ActualCost  ]   [  money  ]  ,
  16       [  ModifiedDate  ]   [  datetime  ]  );
  17   insert   into   @local_variable   (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
  18   SELECT   TOP   50000  TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate  from  Production.TransactionHistoryArchive  ORDER   BY  ReferenceOrderID  ASC 
 19   select   *   from   @local_variable   where  RowNumber  >  ( @PageNumber  -  1 ) *  @PageSize   and  RowNumber  <=   @PageNumber  *  @PageSize 
 20  
 21   SET   @end  =  getdate  ();
  22   PRINT   Datediff (millisecond, @Start , @end );

3、临时表

 --  使用临时表 
 DECLARE   @Start   datetime , @end   datetime  ;
  SET   @Start  =  getdate  ();
  DECLARE   @PageNumber   INT ,  @PageSize   INT ,  @Sql   varchar ( max  );
  SET   @PageNumber  =  5000  ;
  SET   @PageSize  =  10  ;

  create   table  #local_variable(RowNumber  int   identity ( 1 , 1 ), [  TransactionID  ]   [  int  ]  ,
      [  ProductID  ]   [  int  ]  ,
      [  ReferenceOrderID  ]   [  int  ]  ,
      [  ReferenceOrderLineID  ]   [  int  ]  ,
      [  TransactionDate  ]   [  datetime  ]  ,
      [  TransactionType  ]   [  nchar  ] ( 1  ),
      [  Quantity  ]   [  int  ]  ,
      [  ActualCost  ]   [  money  ]  ,
      [  ModifiedDate  ]   [  datetime  ]  );
  insert   into   #local_variable (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate) 
  SELECT   TOP   50000  TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate  from  Production.TransactionHistoryArchive  ORDER   BY  ReferenceOrderID  ASC 
 select   *   from  #local_variable  where  RowNumber  >  ( @PageNumber  -  1 ) *  @PageSize   and  RowNumber  <=   @PageNumber  *  @PageSize 

 SET   @end  =  getdate  ();
  PRINT   Datediff (millisecond, @Start , @end );

4、ROW_NUMBER

 

--使用 ROW_NUMBER
DECLARE @Start datetime,@end datetime;
SET @Start=getdate();

DECLARE @PageNumber INT, @PageSize INT, @Sql varchar(max);
SET @PageNumber=5000;
SET @PageSize=10;
SELECT * FROM
( SELECT ROW_NUMBER()
OVER(ORDER BY ReferenceOrderID) AS RowNumber,
*
FROM Production.TransactionHistoryArchive) AS T
WHERE T.RowNumber<=@PageNumber*@PageSize AND T.RowNumber>(@PageNumber-1)*@PageSize;

SET @end=getdate();
PRINT Datediff(millisecond,@Start,@end);

SQL数据分页技术

标签：

查看更多关于SQL数据分页技术的详细内容...