SQL Data Paging Technology

Data layer data paging three common methods:

 Use AdventureWorks2008

1, Top*top

1 -UseTop*Top2 DECLARE @Start datetime,@end datetime;3 SET @Start=getdate();4 5 DECLARE @PageNumber INT,@PageSize INT,@Sql varchar(Max);6 SET @PageNumber= the;7 SET @Pt=Ten;8 SET @Sql='SELECT t2.* from (9 SELECT TOP ten t1.* fromTen (SELECT TOP' + STR(@PageNumber*@PageSize)+'* from Production.transactionhistoryarchive One ORDER by Referenceorderid ASC) As T1 A ORDER by Referenceorderid DESC) As T2 - ORDER by Referenceorderid ASC'; - EXEC(@sql); the  - SET @end=getdate(); - PRINT Datediff(Millisecond,@Start,@end);

2. Table variables

1 --Working with Table variables2 DECLARE @Start datetime,@end datetime;3 SET @Start=getdate();4 DECLARE @PageNumber INT,@PageSize INT,@Sql varchar(Max);5 SET @PageNumber= the;6 SET @PageSize=Ten;7 8 DECLARE @local_variable Table(RowNumberint Identity(1,1),[TransactionID] [int],9     [ProductID] [int],Ten     [Referenceorderid] [int], One     [Referenceorderlineid] [int], A     [transactiondate] [datetime], -     [Transactiontype] [nchar](1), -     [Quantity] [int], the     [ActualCost] [ Money], -     [ModifiedDate] [datetime]); - Insert  into @local_variable(TransactionID, ProductID, Referenceorderid, Referenceorderlineid, TransactionDate, Transactiontype, Quantity, ActualCost, ModifiedDate) - SELECT TOP 50000TransactionID, ProductID, Referenceorderid, Referenceorderlineid, TransactionDate, Transactiontype, Quantity, ActualCost, ModifiedDate fromProduction.transactionhistoryarchiveORDER  byReferenceorderidASC + Select *  from @local_variable whereRowNumber>(@PageNumber-1)*@PageSize  andRowNumber<= @PageNumber*@PageSize -  + SET @end=getdate(); A PRINT Datediff(Millisecond,@Start,@end);

3. Temporary table

--working with temporary tablesDECLARE @Start datetime,@end datetime;SET @Start=getdate();DECLARE @PageNumber INT,@PageSize INT,@Sql varchar(Max);SET @PageNumber= the;SET @PageSize=Ten;Create Table#local_variable (RowNumberint 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 50000TransactionID, ProductID, Referenceorderid, Referenceorderlineid, TransactionDate, Transactiontype, Quantity, ActualCost, ModifiedDate fromProduction.transactionhistoryarchiveORDER  byReferenceorderidASCSelect *  from#local_variablewhereRowNumber>(@PageNumber-1)*@PageSize  andRowNumber<= @PageNumber*@PageSizeSET @end=getdate();PRINT Datediff(Millisecond,@Start,@end);

4, Row_number

 

--Using 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<[email protected]* @PageSize and T.rownumber> (@PageNumber-1) * @PageSize;

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

SQL Data Paging Technology