How to do back end paging for jqgrid with mssql

Question

Hey guys so I am currently using jqgrid with an mssql database, I am trying to get my paging to work for mssql, for mysql I know you can use the limit command but unfortunately that does not exist in MSSQL, so does anyone know how i can properly page using mssql? Also, i need it to be able to work with search so i cant guarantee a constant number such as row number because the row numbers could vary during search. Thanks!

Answer 1

here's one of the most mssql compare different paging techniques: http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

But I think we are much better off using the entity framework that is solved by using LINQ:

var data = 
  query.Skip((grid.PageIndex - 1) *grid.PageSize).Take(grid.PageSize).ToArray();

from: http://www.codeproject.com/Articles/58357/Using-jqGrid-s-search-toolbar-with-multiple-filter

Answer 2

Ok I got it working, but thank you anyway everyone. For everyone wondering though this is how i did it:

SELECT * FROM(
SELECT TOP $limit *
FROM ( 
  SELECT TOP $nlimit *
  FROM   dbo.computers 
  ORDER BY **FIELD** ASC
) a 
ORDER BY **FIELD** DESC
) a
ORDER BY **FIELD** ASC;