SQL Sorting: server vs. client

Question

Most of the times, when it comes to sort some data, we have two options:

1. Sort on the SQL server -- use ORDER BY clause
2. Sort on client one we get the data from the database

When would you use one over the other and why?

Answer 1

Always sort where possible in the SQL server.

The SQL abstracts away the steps of querying the database, sorting, etc. Use as much abstraction as you can - why would you select a bunch of rows, and then write a sorting algorithm when you can do it in the SQL with ORDER BY.

See also Bill Karwin's answer.

Answer 2

I would use sorting on the server when the dataset is large and I know there's an index in the database that will help with the sort. If there is no index, I would create one.

I would use sorting on the client only if the dataset is small enough to fit comfortably in application memory and the application system has an efficient sorting function, and there is no index in the database. But if the dataset is that small, the cost of sorting in the server even with no index is probably easy to bear.