Server-Side and Client-Side Sorting and Filtering

In dbForge Query Builder for SQL Server, data sorting and filtering can be performed on the client or on the server side.

If there are not many records in a table, they are all shown in the result grid after retrieving data from a table or after executing a SELECT statement. If you want to sort these records or use a filter, the filtering and sorting procedures are performed on the client side and all available records are processed.

But if there are many records in a table, they are not displayed after executing a SELECT statement or retrieving data at once, because such action can cause donwtime of the client machine or some other problems associated with too much data to operate.

The other case when not all records are displayed in the grid is when Paginal Mode is on and there are more records than the number of records displayed on one page. In these cases, if you perform client-side sorting or filtering, only records available on the client machine are processed. That means you will not get a complete result of filtering or sorting. That’s why dbForge Query Builder for SQL Server performs server-side filtering or sorting when not all records can be displayed in the grid.

When server-side filtering or sorting is performed, the application sends a query that is a modification of the starting query to the server. The shortcoming of this way is that server resources are taken but the benefit is that a user will get a complete result of sorting or filtering.

But once all records are received, for example, after pressing CTRL+END or scrolling to the end, server-side filtering and sorting is turned off and client-side filtering and sorting is used.

Problems that may occur during server-side sorting and filtering:

In some cases the application can not modify the starting query to send it to the server and perform server-side sorting or filtering. These cases are the following:

  • There is an expression without an alias in a SELECT statement. Actually, this is a restriction of MS SQL Server - one cannot modify a query that contains a statement without an alias in a SELECT statement. And if there is no alias for a column, one cannot sort or filter data in other columns as well, until all data is fetched or an alias is given.

  • There is a query with an ORDER BY clause. In this case, no server-side filtering can be performed, and it is a SQL Server restriction.