In Query Builder, data sorting and filtering can be performed either on the client or on the server side.
If there are not many records in a table, they are all displayed in the results 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 the available records are processed.
But if there are many records in a table, they are not displayed at once after executing a SELECT statement or retrieving data, because such actions can cause downtime of the client machine or other problems associated with excessive amounts of data.
The other case when not all records are displayed in the grid is when the paginal mode is on and the overall number of records exceeds the number of records displayed on one page. In these cases, if you perform client-side sorting or filtering, only the records that are available on the client machine are processed. This means you will not get a complete result of your sorting or filtering operation. That is why the tool switches to server-side sorting or filtering when not all records can be displayed in the grid.
When server-side sorting or filtering is performed, the application sends a modified version of the input query to the server. Although this operation utilizes server resources, the user gets a complete result of their sorting or filtering operation.
But once all the records are received (for example, after pressing CTRL+END or scrolling to the end), server-side sorting or filtering switches to the client side.
Here are the cases when the application cannot modify the input query and send it to the server to perform server-side sorting or filtering:
There is an expression without an alias in the SELECT statement. Actually, this is a restriction of SQL Server - you cannot modify a query that contains a statement without an alias in a SELECT statement. And if there is no alias for a column, you cannot sort or filter data in other columns as well, until all the data is fetched or an alias is given.
There is a query with an ORDER BY clause. In this case, server-side filtering cannot be performed, which is another SQL Server restriction.