SQL Server Database Paging query

First, CREATE TABLE structure

CREATE TABLE [dbo].[a](    [ID] [int]  not NULL,    [name] [nvarchar]( -)NULL,    [ Age] [int] NULL)

Second, add test data

Declare @i intSet @i=1 while(@i<10000)begin    Insert  intoASelect @i, Left(newid(),7), A    Set @i += 1End

Three, paging SQL, the following example is 10 per page, take 第31-40条 data.

--method One: Order bySelect Top( +- to+1)*  fromAWhereIdinch(Select Top  +Id fromAOrder  byID)Order  byIdDesc--method Two: not In/topSelect Top Ten *  fromAwhereId not inch(Select Top  -Id fromAOrder  byIdOrder  byID--method Three: not EXISTSSelect Top Ten *  fromAwhere  not exists(Select 1  from(Select Top  -Id fromAOrder  byID) A1whereA1.id=a.ID)Order  byID--method Four: Max/topSelect Top Ten *  fromAwhereId>(Select Max(ID) from(Select Top  -Id fromAOrder  byID) A1)Order  byID--method Five: row_number ()Select Top Ten *  from(SelectRow_number () Over(Order  byID) RowNumber,*  froma) A1whereRowNumber> -Select *  from(SelectRow_number () Over(Order  byID) RowNumber,*  froma) A1whereRowNumber> -  andRowNumber< ASelect *  from(SelectRow_number () Over(Order  byID) RowNumber,*  froma) A1whereRowNumberbetween  to  and  +--method Six: Row_number () variant, not based on existing fields to produce the record ordinal, first by criteria filtering and sequencing, and then on the result set to a constant column to generate the record sequence numberSelect * from(SelectRow_number () Over(Order  byID) RowNumber,*  from(Select Top  + *  fromAwhere 1=1 Order  byID) a) bwhereRowNumber> -

Iv. SQL statement Efficiency test

Declare @begin_date datetimeDeclare @end_date datetimeSelect @begin_date = getdate()<..... YOUR CODE .....>Select @end_date = getdate()Select DateDiff(MS,@begin_date,@end_date) as 'milliseconds'

10,000: Basic sense is not the difference

100,000:

V. Conclusion

1.max/top,row_number () is a good paging method. Compared to Row_number () only supports sql2005 and above, Max/top has better portability and can be used for sql2000,access.

2.not exists feeling is a little more efficient than not.

3.row_number () 3 different ways of writing efficiency looks similar.

SQL Server Database Paging query