SQL Server (MSSQL) paging

Today we have evolved a variety of IT development achieved in the biggest of them plugged in the field bojamyeon

Cultural characteristics on web programming will be.

Depending on the server that the language of Galicia and the DBMS, but

Linux (Oracle, Java OR Mysql, PHP), Windows (SQL Server,. Net) and will be.

Many web developers have a great technology comes to web development, but

Chai comes to access to the database are (or are not sure) there are a lot of parts.

One of them is now described paging.

Of course, that some career comes to Web developers have to handle part of course, but

* DB for beginner developers or developers who do not have much interest must not be handled in a flexible part.

When developing the first web developers put some dummy data will be tested.

But later on, depending on the amount of dummy data to the server can give a big load.

In general, the dummy data at least 10 million (the date of the current Internet, progress and

Depending on the amount of website visitors, but only if you see a board with 10 million or more data accumulate

There are many servers), it is better to use the data.

One to two hundred ten thousand days of test and put dummy data 'Oh, hoppin', and I think if folding

Coming to see the bathroom, clean up that mess with people not doing exactly the same if you can Rigaud.

From now on, let's learn about simple paging mechanism

A. NOT IN (TOP)

SELECT TOP page size *
FROM table_name
WHERE id NOT IN
(
SELECT TOP (page -1) * page size id list
FROM table_name
)

Two. ROW_NUMBER

SELECT *
FROM
(
SELECT ROWNUMBER () OVER (ORDER BY id DESC) rnum, *
FROM table_name)
) A
WHERE rnum BETWEEN (page size * page list size) + 1 AND (page size, page list * size) + (page size + 1)

Three. Use DECLARE

DECLARE @ id int

SELECT TOP (page size * list size + 1) id
FROM table_name

SELECT TOP page size *
FROM table_name
WHERE id> @ id

Four. TOP TOP

SELECT TOP page size *
FROM
(
SELECT TOP (page size * page list size + 1) *
FROM table_name)
) A

Baejaehan query, the query above is part of the sort.

Of course, the id of the index column or PK is the

Whether used in any way personally, but individual parts 3 and 4 times is recommended.



Why should you use paging?

If the data that you have a million and a 10 per page 30 shows the data, suppose

In fact, the client (user) data to show only 30 cases, is only

If you divide the data into the paging processing of 10 million cans of full-time is very unfortunate that

Will result.