tymberwyld (1/6/2009)
thierry.vandurme (1/6/2009)
I don't really understand how this reduces server resource utilization except for network traffic. If a user is paging through the data screen, only a screen full of rows (10,20...) will transmit over the network but the query will be executed each time the user clicks on page down. Maybe I'm missing something?Simple, most Pagination routines are processed on the client. What Adam is getting at here is that instead of sending all 1,000,000 rows back through the Network to the Client for every, single Page a user clicks on, you are only sending back the rows the user is requesting. This not only reduces the load on your server but not to mention your application servers (mostly IIS hosting web sites).
Adam, the only additional thing I didn't see was how to Sort by multiple columns. What if I want "LastName ASC, FirstName DESC"?
Unfortunately, you will have to use a dynamic SQL to sort by more than one column. You will need to use the SortCol variable in place of the case expression. You will also need to remove the parameterized @sort column form the sp_executesql command.
Here is a sample:
ROW_NUMBER() OVER(ORDER BY ' + @SortCol + ') AS [Row_ID],
Forgot to mention that doing this will greatly reduce parameterization optimization and open up a security hole. Make sure to validate all input variables for malicious intent.