ROW_NUMBER 와 OFFSET ~ FETCH NEXT 페이징 비교

row_number 값으로 between 으로 페이징 처리하는 방법과 OFFSET ~ FETCH NEXT 구문으로 페이징 처리 하는 방법입니다. 붉은색으로 되어 있는 부분이 페이지 번호이며, 10 으로 표기된 부분은 페이징사이즈 입니다.

WHERE PagedNUMBER BETWEEN ((1 - 1) * 10 + 1) AND (1 * 10)  ORDER BY ListID;
WHERE PagedNUMBER BETWEEN ((2 - 1) * 10 + 1) AND (2 * 10)  ORDER BY ListID;
WHERE PagedNUMBER BETWEEN ((3 - 1) * 10 + 1) AND (3 * 10)  ORDER BY ListID;

---

OFFSET ((1 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
OFFSET ((2 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
OFFSET ((3 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
OFFSET ((4 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY

	SELECT *,
	(SELECT COUNT(1) FROM UNIV_BOA_Attach WHERE ListID = BL.ListID) as AttachCount,
	(SELECT COUNT(1) FROM UNIV_BOA_ShortReply WHERE ListID = BL.ListID) as ShortCount,
	(SELECT PhotoPath FROM IdentityUsers WITH(NOLOCK)  WHERE UserID = BL.UserID) as PhotoPath
	FROM
	(
			SELECT ROW_NUMBER() OVER(ORDER BY ListID) AS PagedNumber, 
				BL.CategoryID, BC.CategoryName, ListID, GroupID, 
				ListOrder, Depth, Subject, MobileContent, OtherModify, 
				BL.UseTop, BL.UseTopDate, BC.UsePopup, BL.UsePopupDate, BL.UseAnonymity, BL.ReadCount, 
				BL.ScreenShot, BL.HeaderID, H.HeaderName, BL.UserID, BL.Name, BL.WriteDate
			FROM UNIV_BOA_List AS BL INNER JOIN UNIV_BOA_Category AS BC  ON BL.CategoryID = BC.CategoryID
			LEFT OUTER JOIN UNIV_BOA_Header H ON BL.HeaderID = H.HeaderID
			WHERE BL.CategoryID IN(1) and Subject LIKE N'%대학내일%'
	) AS BL
	WHERE PagedNUMBER BETWEEN ((1 - 1) * 10 + 1) AND (1 * 10)  ORDER BY ListID;
	--WHERE PagedNUMBER BETWEEN ((2 - 1) * 10 + 1) AND (2 * 10)  ORDER BY ListID;
	--WHERE PagedNUMBER BETWEEN ((3 - 1) * 10 + 1) AND (3 * 10)  ORDER BY ListID;
	--WHERE PagedNUMBER BETWEEN ((4 - 1) * 10 + 1) AND (4 * 10)  ORDER BY ListID;
	--WHERE PagedNUMBER BETWEEN ((5 - 1) * 10 + 1) AND (5 * 10)  ORDER BY ListID;

---

	SELECT ROW_NUMBER() OVER(ORDER BY ListID) AS PagedNumber, 
		(SELECT COUNT(1) FROM UNIV_BOA_Attach WHERE ListID = BL.ListID) as AttachCount,
		(SELECT COUNT(1) FROM UNIV_BOA_ShortReply WHERE ListID = BL.ListID) as ShortCount,
		(SELECT PhotoPath FROM IdentityUsers WITH(NOLOCK)  WHERE UserID = BL.UserID) as PhotoPath,
		BL.CategoryID, BC.CategoryName, ListID, GroupID, 
		ListOrder, Depth, Subject, MobileContent, OtherModify, 
		BL.UseTop, BL.UseTopDate, BC.UsePopup, BL.UsePopupDate, BL.UseAnonymity, BL.ReadCount, 
		BL.ScreenShot, BL.HeaderID, H.HeaderName, BL.UserID, BL.Name, BL.WriteDate
	FROM UNIV_BOA_List AS BL INNER JOIN UNIV_BOA_Category AS BC  ON BL.CategoryID = BC.CategoryID
	LEFT OUTER JOIN UNIV_BOA_Header H ON BL.HeaderID = H.HeaderID
	WHERE BL.CategoryID IN(1) and Subject LIKE N'%대학내일%'
	ORDER BY ListID
	OFFSET ((1 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
	--OFFSET ((2 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
	--OFFSET ((3 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
	--OFFSET ((4 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY
	--OFFSET ((5 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY