DB관련/SQL Server

SQL서버에서 paginate 만들기 쿼리

saltdoll 2018. 5. 8. 06:25
반응형

SQL 서버에서 페이지 나눔 처리에 대해 쿼리를 분석해 본다.

SQL서버는 일반적으로 auto increment idx번호를 부여하지 않고 사용하여 설계되는 경우가 많습니다.


Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.


참고: https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

반응형
도움이 되셨다면 하트모양의 "♡ 공감"을 눌러주시면 큰 격려가 됩니다.
(로그인하지 않으셔도 가능)