I have an n-tier application which among other things includes a Data Access Layer (DAL) AND a Business Logic Layer (BLL). I use SQL queries and stored procs in the DAL which I use to fill my objects.
So here's my question:
Does an ORDER BY clause in my SQL queries violate separation of concerns?
On the one hand, it seems that sorting logic belongs in the business layer because it is our business rules that determine why we want to display the data in a particular order. Also, we may want to display the same data in more than one way. Further, shouldn't my data access code be unaware of concerns such as how it is displayed?
On the other hand databases are generally more efficient at sorting data than application code, so for performance reasons there is an incentive go with an ORDER BY clause over sorting in the BLL. In addition, I'm not sure if specifying a default sort order in the DAL really violates separation of concerns. The records have to come out of the database in some manner. Why not sort them according to the most common scenario? In cases where we need a different sort order than the default, then we can sort via some method in the BLL.
I feel it's OK if you have a 'default' sort setting which is applied in the absence of sort settings being provided to the procedure.
That will make the code re-usable and flexible going forwards....what if, for example, you wanted to allow users to define the sort settings themselves?
I don't think it's a violation, considering many tables have obvious default sorting logic that would otherwise need to be re-applied time and time again. And you are correct in saying it's more effective to apply sorting at the database level.