SQL Server (MSSQL) paging techniques

Today, we made the biggest development of the sector in the evolution of the various IT plugged bojamyeon

Will include web programming.

Ghali of the server, depending on the language and DBMS, but

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

A great many web developers web development, but technology has

Be ruled out (or do not know) and there are a lot of access to the database.

Paging mechanism to explain one of them from now on.

Winning web developers to some extent, of course, of course, to be done, but

Beginner Developer or do not show much interest in * DB developers will not be part of a flexible processing.

Dummy data to some extent, when the Web was developed for the first time, the developer will put to test.

Large load on the server later, depending on the amount of data, but the dummy line.

Typically, 10 million more than the dummy data (the current Internet's evolving and,

Web site visitors, depending on the amount one will differ Wall bodeorado accumulating more than 10 million data

Server is quite often a good idea to use the data).

You put a pile of 200 data test and think, 'Oh well.' Folding if

Rigaud be just like the people coming behind doing the bathroom.

Now let's take a look from a simple paging technique

1 NOT IN (TOP)

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

2 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 list of page size) + size * (page size + 1)

3 Use DECLARE

DECLARE @ id int

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

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

4 TOP TOP

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

Sort the above query exclude the portion of the query.

Of course id is the column index or PK,

Any way how to use the individual parts, but 3 times and 4 times the recommended personally.



Why should I use the paging?

The data that 10 million data per page 30 boyeojundago let

Only 30 actually show the data to the client (user) is only

Full scan every time paging divide data processing if 10 million is a very unfortunate

Will result.