Between code (C#, Java) and database (SQL) where should we sort or filter?

Between code (C#, Java) and database (SQL) where should we sort or filter?

In code it means having a single stored procedure to retrieve all data from the database and then filter my data (in the busine开发者_StackOverflow中文版ss or data layer).

In database it means having a stored procedure. In this case the code (data layer) send my filter settings to stored procedure that takes care to return a lightened data.

What is better and in what circumstances?

It really depends on the nature of the usage. Example:

• Data size
• Fetch Frequency
• Response-time to user
• Etc etc

Generally, let the DB server do as much data related operation as you can, they are optimized for it. But it's not a definite rule. In many cases, batch of data are retrieved in one go, preseented and sort/filtered at the UI layer/application-side. E.g. For small table sorting on web pages for faster response-time.

In general you should create your request in your code and let the DB server do the job. For specific case you can also decide to filter in you client class.