1

We have SQL Server 2016 as the backend for our application with a _SC collation. The application itself is "locales aware" and supports a customer operating in multiple locales. Thus the data in the database can be in "any language". There is a request to page the data displayed in the UI on sort order based on the users locale across the entire result set. Rather than pass the entire result set to the higher layers and let them do the paging, the team is evaluating

  1. Passing the culture from the UI layer, across different layers right down to the database,
  2. map the culture to a specific Collation at the SQL Server and
  3. have the SQL Server sort & page based on this mapped Collation. Are there better ways to do this OR is this not a good idea and we are better off letting the higher layers be responsible for paging? Per the guidelines we have, sorting is prohibited in the database but I guess this is one use case where these guidelines ought to be relaxed.

I have researched Does Microsoft SQL Server support localized name sorting? and https://social.technet.microsoft.com/wiki/contents/articles/31194.t-sql-sort-data-by-multiple-languages.aspx and this concept is a "union" of these 2 approaches

Thom A
  • 88,727
  • 11
  • 45
  • 75
QFirstLast
  • 229
  • 1
  • 10
  • It's not clear in Step 2: do you always simply want to sort based on the collation of the user's locale, or do you want to define custom sorting rules based on the users locale? Examples might help. – Tab Alleman Sep 20 '19 at 18:41
  • 1
    Good news: regardless of what solution you pick, performance will suck anyway since no layer (including the database) can make effective use of indexes this way, so you can do whatever is most convenient. Note that, while not passing the whole result set does improve matters, you will still have SQL Server working its poor behind off to sort whole tables based on whatever the user wants this time. Pray they don't actually go back and forth on pages too much. – Jeroen Mostert Sep 20 '19 at 19:41
  • @TabAlleman, We want to sort based on the collation of the user's locale.We haven't though about custom sorting...Can you clarify when this comes into play ? – QFirstLast Sep 20 '19 at 21:03
  • @Jeroen Mostert, I do understand some or the other layer has to pay. The "gut feel" is it would be cheaper to do it at the SQL Server. Before testing this theory, I want to check if the community has a strong opinion against it. – QFirstLast Sep 20 '19 at 21:08

1 Answers1

0

The DB will do the best job of sorting so pass it down. Any other methods will involve returning all results to a middle layer, so expensive I terms or resources and reinventing things DBs are designed to do.

If your guidelines suggest not using the DB for sorting the change your guidelines. That's just dumb.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
  • some unfortunate books on ASP.NET were showing examples of reading the whole dataset and doing sorting and paging in-memory. – Dr Phil Sep 20 '19 at 19:39