Pagination like Google with MSSQL
- When people search for data, most of the time, they do not need all the data returned.
- With the right selection criteria and right sorting order, often the data toward the beginning will be what they were looking for.
- Why send across the network or the Internet 200,000 rows?
- MSSQL 2005 can easily send data like Google 10 entries at a time, by using the row number function.
- The pagination is what allows Google to serve so many people. By returning only 10 entries at the time, Google can serve millions of requests.
- Google retrieves only between 6k and 20k of data from their database for each search request.
- Google sends only between 6k and 20k of data through the Internet for each search request.
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- SELECT empno,projno,actno,emptime,emstdate,emendate
- FROM emp_work;
- go
empno projno actno emptime emstdate emendate ------ ------ ------ --------------------------------------- ----------------------- ----------------------- 000010 MA2100 10 0.50 2002-01-01 00:00:00.000 2002-11-01 00:00:00.000 000010 MA2110 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000010 AD3100 10 0.50 2002-01-01 00:00:00.000 2002-07-01 00:00:00.000 000020 PL2100 30 1.00 2002-01-01 00:00:00.000 2002-09-15 00:00:00.000 000030 IF1000 10 0.50 2002-06-01 00:00:00.000 2003-01-01 00:00:00.000 000030 IF2000 10 0.50 2002-01-01 00:00:00.000 2003-01-01 00:00:00.000 000050 OP1000 10 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000050 OP2010 10 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000070 AD3110 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000090 OP1010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000100 OP2010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000110 MA2100 20 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 000130 IF1000 90 1.00 2002-01-01 00:00:00.000 2002-10-01 00:00:00.000 000130 IF1000 100 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 000140 IF1000 90 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 000140 IF2000 100 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 000140 IF2000 100 0.50 2002-03-01 00:00:00.000 2002-07-01 00:00:00.000 000140 IF2000 110 0.50 2002-03-01 00:00:00.000 2002-07-01 00:00:00.000 000140 IF2000 110 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 000150 MA2112 60 1.00 2002-01-01 00:00:00.000 2002-07-15 00:00:00.000 000150 MA2112 180 1.00 2002-07-15 00:00:00.000 2003-02-01 00:00:00.000 000160 MA2113 60 1.00 2002-07-15 00:00:00.000 2003-02-01 00:00:00.000 000170 MA2112 60 1.00 2002-01-01 00:00:00.000 2003-06-01 00:00:00.000 000170 MA2112 70 1.00 2002-06-01 00:00:00.000 2003-02-01 00:00:00.000 000170 MA2113 80 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000180 MA2113 70 1.00 2002-04-01 00:00:00.000 2002-06-15 00:00:00.000 000190 MA2112 70 1.00 2002-02-01 00:00:00.000 2002-10-01 00:00:00.000 000190 MA2112 80 1.00 2002-10-01 00:00:00.000 2003-10-01 00:00:00.000 000200 MA2111 50 1.00 2002-01-01 00:00:00.000 2002-06-15 00:00:00.000 000200 MA2111 60 1.00 2002-06-15 00:00:00.000 2003-02-01 00:00:00.000 000210 MA2113 80 0.50 2002-10-01 00:00:00.000 2003-02-01 00:00:00.000 000210 MA2113 180 0.50 2002-10-01 00:00:00.000 2003-02-01 00:00:00.000 000220 MA2111 40 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000230 AD3111 60 1.00 2002-01-01 00:00:00.000 2002-03-15 00:00:00.000 000230 AD3111 60 0.50 2002-03-15 00:00:00.000 2002-04-15 00:00:00.000 000230 AD3111 70 0.50 2002-03-15 00:00:00.000 2002-10-15 00:00:00.000 000230 AD3111 80 0.50 2002-04-15 00:00:00.000 2002-10-15 00:00:00.000 000230 AD3111 180 1.00 2002-10-15 00:00:00.000 2003-01-01 00:00:00.000 000240 AD3111 70 1.00 2002-02-15 00:00:00.000 2002-09-15 00:00:00.000 000240 AD3111 80 1.00 2002-09-15 00:00:00.000 2003-01-01 00:00:00.000 000250 AD3112 60 1.00 2002-01-01 00:00:00.000 2002-02-01 00:00:00.000 000250 AD3112 60 0.50 2002-02-01 00:00:00.000 2002-03-15 00:00:00.000 000250 AD3112 60 0.50 2002-12-01 00:00:00.000 2003-01-01 00:00:00.000 000250 AD3112 60 1.00 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 000250 AD3112 70 0.50 2002-02-01 00:00:00.000 2002-03-15 00:00:00.000 000250 AD3112 70 1.00 2002-03-15 00:00:00.000 2002-08-15 00:00:00.000 000250 AD3112 70 0.25 2002-08-15 00:00:00.000 2002-10-15 00:00:00.000 000250 AD3112 80 0.25 2002-08-15 00:00:00.000 2002-10-15 00:00:00.000 000250 AD3112 80 0.50 2002-10-15 00:00:00.000 2002-12-01 00:00:00.000 000250 AD3112 180 0.50 2002-08-15 00:00:00.000 2003-01-01 00:00:00.000 000260 AD3113 70 0.50 2002-06-15 00:00:00.000 2002-07-01 00:00:00.000 000260 AD3113 70 1.00 2002-07-01 00:00:00.000 2003-02-01 00:00:00.000 000260 AD3113 80 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 000260 AD3113 80 0.50 2002-03-01 00:00:00.000 2002-04-15 00:00:00.000 000260 AD3113 180 0.50 2002-03-01 00:00:00.000 2002-04-15 00:00:00.000 000260 AD3113 180 1.00 2002-04-15 00:00:00.000 2002-06-01 00:00:00.000 000260 AD3113 180 0.50 2002-06-01 00:00:00.000 2002-07-01 00:00:00.000 000270 AD3113 60 0.50 2002-03-01 00:00:00.000 2002-04-01 00:00:00.000 000270 AD3113 60 1.00 2002-04-01 00:00:00.000 2002-09-01 00:00:00.000 000270 AD3113 60 0.25 2002-09-01 00:00:00.000 2002-10-15 00:00:00.000 000270 AD3113 70 0.75 2002-09-01 00:00:00.000 2002-10-15 00:00:00.000 000270 AD3113 70 1.00 2002-10-15 00:00:00.000 2003-02-01 00:00:00.000 000270 AD3113 80 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 000270 AD3113 80 0.50 2002-03-01 00:00:00.000 2002-04-01 00:00:00.000 000280 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000290 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000300 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000310 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000320 OP2011 140 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000320 OP2011 150 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000330 OP2012 140 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000330 OP2012 160 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000340 OP2013 140 0.50 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000340 OP2013 170 0.50 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000020 PL2100 30 1.00 2002-01-01 00:00:00.000 2002-09-15 00:00:00.000 (75 row(s) affected)
Row number
- Microsoft SQL Server 2005 and up have the row number function, that allows us to count each row on the fly.
- SELECT row_number() OVER (ORDER BY empno, emstdate) AS ROW,
- empno,projno,actno,emptime,emstdate,emendate
- FROM emp_work;
- go
Row empno projno actno emptime emstdate emendate -------------------- ------ ------ ------ --------------------------------------- ----------------------- ----------------------- 1 000010 MA2100 10 0.50 2002-01-01 00:00:00.000 2002-11-01 00:00:00.000 2 000010 MA2110 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 3 000010 AD3100 10 0.50 2002-01-01 00:00:00.000 2002-07-01 00:00:00.000 4 000020 PL2100 30 1.00 2002-01-01 00:00:00.000 2002-09-15 00:00:00.000 5 000020 PL2100 30 1.00 2002-01-01 00:00:00.000 2002-09-15 00:00:00.000 6 000030 IF2000 10 0.50 2002-01-01 00:00:00.000 2003-01-01 00:00:00.000 7 000030 IF1000 10 0.50 2002-06-01 00:00:00.000 2003-01-01 00:00:00.000 8 000050 OP1000 10 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 9 000050 OP2010 10 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 10 000070 AD3110 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 11 000090 OP1010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 12 000100 OP2010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 13 000110 MA2100 20 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 14 000130 IF1000 90 1.00 2002-01-01 00:00:00.000 2002-10-01 00:00:00.000 15 000130 IF1000 100 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 16 000140 IF2000 100 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 17 000140 IF2000 100 0.50 2002-03-01 00:00:00.000 2002-07-01 00:00:00.000 18 000140 IF2000 110 0.50 2002-03-01 00:00:00.000 2002-07-01 00:00:00.000 19 000140 IF2000 110 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 20 000140 IF1000 90 0.50 2002-10-01 00:00:00.000 2003-01-01 00:00:00.000 21 000150 MA2112 60 1.00 2002-01-01 00:00:00.000 2002-07-15 00:00:00.000 22 000150 MA2112 180 1.00 2002-07-15 00:00:00.000 2003-02-01 00:00:00.000 23 000160 MA2113 60 1.00 2002-07-15 00:00:00.000 2003-02-01 00:00:00.000 24 000170 MA2112 60 1.00 2002-01-01 00:00:00.000 2003-06-01 00:00:00.000 25 000170 MA2113 80 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 26 000170 MA2112 70 1.00 2002-06-01 00:00:00.000 2003-02-01 00:00:00.000 27 000180 MA2113 70 1.00 2002-04-01 00:00:00.000 2002-06-15 00:00:00.000 28 000190 MA2112 70 1.00 2002-02-01 00:00:00.000 2002-10-01 00:00:00.000 29 000190 MA2112 80 1.00 2002-10-01 00:00:00.000 2003-10-01 00:00:00.000 30 000200 MA2111 50 1.00 2002-01-01 00:00:00.000 2002-06-15 00:00:00.000 31 000200 MA2111 60 1.00 2002-06-15 00:00:00.000 2003-02-01 00:00:00.000 32 000210 MA2113 80 0.50 2002-10-01 00:00:00.000 2003-02-01 00:00:00.000 33 000210 MA2113 180 0.50 2002-10-01 00:00:00.000 2003-02-01 00:00:00.000 34 000220 MA2111 40 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 35 000230 AD3111 60 1.00 2002-01-01 00:00:00.000 2002-03-15 00:00:00.000 36 000230 AD3111 60 0.50 2002-03-15 00:00:00.000 2002-04-15 00:00:00.000 37 000230 AD3111 70 0.50 2002-03-15 00:00:00.000 2002-10-15 00:00:00.000 38 000230 AD3111 80 0.50 2002-04-15 00:00:00.000 2002-10-15 00:00:00.000 39 000230 AD3111 180 1.00 2002-10-15 00:00:00.000 2003-01-01 00:00:00.000 40 000240 AD3111 70 1.00 2002-02-15 00:00:00.000 2002-09-15 00:00:00.000 41 000240 AD3111 80 1.00 2002-09-15 00:00:00.000 2003-01-01 00:00:00.000 42 000250 AD3112 60 1.00 2002-01-01 00:00:00.000 2002-02-01 00:00:00.000 43 000250 AD3112 60 0.50 2002-02-01 00:00:00.000 2002-03-15 00:00:00.000 44 000250 AD3112 70 0.50 2002-02-01 00:00:00.000 2002-03-15 00:00:00.000 45 000250 AD3112 70 1.00 2002-03-15 00:00:00.000 2002-08-15 00:00:00.000 46 000250 AD3112 70 0.25 2002-08-15 00:00:00.000 2002-10-15 00:00:00.000 47 000250 AD3112 80 0.25 2002-08-15 00:00:00.000 2002-10-15 00:00:00.000 48 000250 AD3112 180 0.50 2002-08-15 00:00:00.000 2003-01-01 00:00:00.000 49 000250 AD3112 80 0.50 2002-10-15 00:00:00.000 2002-12-01 00:00:00.000 50 000250 AD3112 60 0.50 2002-12-01 00:00:00.000 2003-01-01 00:00:00.000 51 000250 AD3112 60 1.00 2003-01-01 00:00:00.000 2003-02-01 00:00:00.000 52 000260 AD3113 80 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 53 000260 AD3113 80 0.50 2002-03-01 00:00:00.000 2002-04-15 00:00:00.000 54 000260 AD3113 180 0.50 2002-03-01 00:00:00.000 2002-04-15 00:00:00.000 55 000260 AD3113 180 1.00 2002-04-15 00:00:00.000 2002-06-01 00:00:00.000 56 000260 AD3113 180 0.50 2002-06-01 00:00:00.000 2002-07-01 00:00:00.000 57 000260 AD3113 70 0.50 2002-06-15 00:00:00.000 2002-07-01 00:00:00.000 58 000260 AD3113 70 1.00 2002-07-01 00:00:00.000 2003-02-01 00:00:00.000 59 000270 AD3113 80 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 60 000270 AD3113 80 0.50 2002-03-01 00:00:00.000 2002-04-01 00:00:00.000 61 000270 AD3113 60 0.50 2002-03-01 00:00:00.000 2002-04-01 00:00:00.000 62 000270 AD3113 60 1.00 2002-04-01 00:00:00.000 2002-09-01 00:00:00.000 63 000270 AD3113 60 0.25 2002-09-01 00:00:00.000 2002-10-15 00:00:00.000 64 000270 AD3113 70 0.75 2002-09-01 00:00:00.000 2002-10-15 00:00:00.000 65 000270 AD3113 70 1.00 2002-10-15 00:00:00.000 2003-02-01 00:00:00.000 66 000280 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 67 000290 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 68 000300 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 69 000310 OP1010 130 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 70 000320 OP2011 140 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 71 000320 OP2011 150 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 72 000330 OP2012 140 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 73 000330 OP2012 160 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 74 000340 OP2013 140 0.50 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 75 000340 OP2013 170 0.50 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 (75 row(s) affected)
- Line 1: select row_number() over (order by empno, emstdate) as Row
- row_number(): is the function that will number the rows
- over (order by empno, emstdate): is the order on how to number the rows. It does not have to match the sort order clause of the select statement. It can be any order that you want. If the order by of the over is different than a covering index, it will generate a sort for the row numbering, which is different from the sort done in the sort clause of the select statement.
Pagination through the row number
- SELECT empno,projno,actno,emptime,emstdate,emendate FROM (
- SELECT row_number() OVER (ORDER BY empno, emstdate) AS ROW,
- empno,projno,actno,emptime,emstdate,emendate
- FROM emp_work) abc
- WHERE ROW between 5 and 14;
- go
empno projno actno emptime emstdate emendate ------ ------ ------ --------------------------------------- ----------------------- ----------------------- 000020 PL2100 30 1.00 2002-01-01 00:00:00.000 2002-09-15 00:00:00.000 000030 IF2000 10 0.50 2002-01-01 00:00:00.000 2003-01-01 00:00:00.000 000030 IF1000 10 0.50 2002-06-01 00:00:00.000 2003-01-01 00:00:00.000 000050 OP1000 10 0.25 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000050 OP2010 10 0.75 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000070 AD3110 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000090 OP1010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000100 OP2010 10 1.00 2002-01-01 00:00:00.000 2003-02-01 00:00:00.000 000110 MA2100 20 1.00 2002-01-01 00:00:00.000 2002-03-01 00:00:00.000 000130 IF1000 90 1.00 2002-01-01 00:00:00.000 2002-10-01 00:00:00.000 (10 row(s) affected)
- where Row between 5 and 14: This showed the 10 rows between 5 and 14.
- By placing this query in a stored procedure, you can change the 5 and 14 with @startrow and @endrow. Now you can control what these values will be.
- To do a where on a calculated column, you will either have to use a subquery or a derived table because the where is executed before the calculated column is calculated.
Pagination with SQL Server 2000
- SQL Server 2000 does not support the row_number function.
- There are a few ways to do that.
- The easiest is to create a temporary table with a unique increment key called row number, then do an insert into that temporary table.