MS-SQL- 게시판 페이징 쿼리

MSSQL 전용 게시판 페이징 처리 쿼리 1. row_number() (버젼에 따라 함수가 없을수도 있음) 데이터가 많은 경우 유용

select * from(
select Row_Number() over(order by sFaq_index desc)as rownum,* from 테이블명 where sFaq_index like '%1%') 테이블명
where rownum BETWEEN [(페이지번호-1)*페이지크기+1] and [페이지번호*페이지크기]

2. not in 절 데이터가 대량으로 갈수록 느려짐

select top 10 * from qas where g_num not in (select ((page-1)*pagesize) g_num from qas where g_num in (select g_num from qas where g_name like '%moon74%')) and g_num in (select g_num from qas where g_name like '%moon74%') order by g_num desc

select top [pagesize] from [table] where [PK] not in ( select top [(page-1)*pagesize] from [table] where [조건] oder by [pk] desc ) order by [pk] desc

3. TOP,MIn 사용

  SQL = "SELECT TOP " & pagesize & " * FROM " & board_name
  SQL = SQL & " WHERE " & kind & " like '%" & search & "%' and g_num <= (SELECT MIN(g_num)"
  SQL = SQL & " FROM (SELECT TOP " & ((page - 1) * pagesize + 1) & " g_num FROM " & board_name & " where " & kind & " like '%" & search & "%' order by g_num desc) AS A)"