[mssql] Server Side Paging

The script below can be used to fetch the record according to certain page number and size. The syntax is:

  SELECT {field_list} FROM
    (SELECT TOP {page_size} {field_list} FROM
      (SELECT TOP {page_size * page_number} {field_list}
        FROM {table} (NOLOCK)
        WHERE {where_criteria}
        ORDER BY {field_to_order} {order_type}
      ) AS SortedData
      ORDER BY {field_to_order} {reverse_order_type}
    ) AS PagedData
    ORDER BY {field_to_order} {order_type}

Let's say we would like to fetch the data from table customer for page number 10 where the page size is 20, which descending order by username.

  SELECT username, fullname FROM
    (SELECT TOP 20 username, fullname FROM
      (SELECT TOP 200 username, fullname
        FROM customer (NOLOCK)
        WHERE fullname like '%ali%'
        ORDER BY username DESC
      ) AS SortedData
      ORDER BY username ASC
    ) AS PagedData
    ORDER BY main_username DESC