SQL Server Paging ISSUE.

SQL서버에서 페이징을 고민 하시는 분들이라면 한번쯤 아래의 포스트를 참조 하기 바랍니다.

SQL Server Paging ISSUE.

http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

--SQL 2000 Paging Method
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

            
CREATE TABLE #employees (RowNumber INT IDENTITY(1,1),
                LastName VARCHAR(100),FirstName VARCHAR(100),
                EmailAddress VARCHAR(100))

  
INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress 
  FROM Employee 
 ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress 
  FROM #employees
 WHERE RowNumber > @Start AND RowNumber <= @End

  
DROP TABLE #employees

  
GO

  
--SQL 2005/2008 Paging Method Using Derived Table
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

  
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
      FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL 2005/2008 Paging Method Using CTE
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;

  
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
 FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL SERVER 2012
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;