Server-Side and Client-Side Sorting and Filtering

In Data Compare for SQL Server, sorting and filtering of data 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 the 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 the SELECT statement or retrieving data at once, because such action can cause hanging 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 then 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 Data Compare 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 alias in the SELECT clause. Actually this is a restriction of MS SQL Server - one cannot modify a query that contains a statement without an alias in the SELECT clause. 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 the ORDER BY clause. In this case no server-side filtering can be performed, and it is a SQL Server restriction.