Author: Li root Microsoft MVP
1. I personally think the best paging method is:
SELECT
TOP
10
*
From
TABLE
WHERE
id
>
200
Write into a stored procedure, the above statement wants to spell a SQL statement, to get the last greater than which ID number 2. The way to use the cursor is only suitable for the table of small data volume. If the table is above 10,000 lines, it is bad for your stored procedure than not in paging, example:
SELECT
TOP
10
*
From
Customers
WHERE
Customerid
NOT
In
(
SELECT
TOP
20
Customerid
From
Customers)
Declare
@Sqlstr
VARCHAR
(
8000
)
set
@Sqlstr
=
'
SELECT TOP
'
CAST
(@ 每 每
AS
VARCHAR
)
'
* FROM table Where main key column Not in (SELECT TOP
'
CAST
(@ 每 每
*
@which page
AS
VARCHAR
)
'
Primary key list from FROM table)
'
EXEC
(@SQLSTR)
3. Here is the way I use the stored procedure paging, paginating a table of 200,000, the result is displayed in the web page, you can take a look at the speed:
http://www.bizlands.com/trade/search.asp?type=s&sortid=15
4. I have been a long time, I said to use SQL's cursors.
The cursor is stored in memory and is very consumed.
The cursor is built, and the relevant record is locked until the cursor is canceled.
The cursor provides a means of scanning a row of row by line, generally uses a cursor to cross data, and perform different operations depending on the different data conditions. The cycle of the cursor (large data set) defined in multi-table and big tables is easy to enter a long wait or even crash.
So, my personal experience is 10,000 upstairs tables, no cursor. Small data volume, you can use a cursor
Because of the cursor, it is still a good way to traverse small data volume!
5. Customize paging with temporary tables is not good!
