January 11, 2017 at 7:00 am
Hi all,
I see a few posts where it's mentioned that certain processing, like 24 to 12 hr Time should properly occur in the application layer.
Is there an article or list of best practices about what should happen where?
Thanks,
JB
January 11, 2017 at 7:07 am
Ideally, formatting for display done where and when the data is displayed, not in the database. The database should care about the data, not it's visual representation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2017 at 7:24 am
GilaMonster (1/11/2017)
Ideally, formatting for display done where and when the data is displayed, not in the database. The database should care about the data, not it's visual representation.
Hi - are you the same Gail Shaw on Youtube doing presentations? Very well done if so 🙂
I should have phrased my question better - what should be done in SQL Server, vs outside? Now I think about it, presumably SSRS often houses a lot of code used to sort/slice data for more effective display.
January 11, 2017 at 7:32 am
First, definitely don't store pre-formatted data (ex: "$1200.50" or "01/11/2017 09:12AM") in the table as VARCHAR columns. That's a bad idea because it obstructs your ability to sum, evaluate, and sort the data correctly using SQL expressions.
If for some reason it would be impractical to format the data for display on the client side, like when executive level users are running ad-hoc queries and can't (or won't) do the effort on their end, then contain your SQL select statements within views or stored procedures, so at least it's consistent and coded properly. What I've done in the past is basically have two sets of views: one set of base views that selects the data without any formatting, and this is what gets used by the application, and then another set of views that wraps around the base views just for the purpose of adding an additional layer of formatting and/or filtering to support ad-hoc querying.
We can argue architectural philosophy, but it's kind of like when the kitchen staff at a restaurant receives a customer requests to have their steak pre-cut into tiny bite sized cubes. Yes, really the customer should be cutting their own steak, but sometimes you just have to roll with it and let the customer be right, especially if the customer in question owns the business.
:rolleyes:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 12, 2017 at 12:49 pm
There is a distinct difference between converting data and formatting it.
Formatting should nearly always be done at the presentation layer (application or report). This generally allows your presentation layer sorting to be aware of the data. For example, displaying a month name is better done as formatting so the report can still use the underlying date for sorting. If you convert to a month name in the database, you'll need to jump through some hoops to properly sort it in the presentation layer.
If the conversion is for functionality but doesn't change the meaning of the data, do it wherever it is fastest. In one of my applications, the vendor used an int to store a numeric account number, but I convert it to a string and pad the zeros in my query before passing it to the application. I found that SQL could perform this action much quicker then having my app do the conversion and concatenation for every row returned.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply