Format report data in the app or in SQL?

Question

Format report data in the app or in SQL?

J Livingston SQL

SSC Guru

Points: 51550
More actions
July 21, 2010 at 1:03 pm

#141471

Hi
reading a recent post on this site, got me to thinking about how I currently present data to users....
and one particular comment caught my eye...that I appear to have 1950's mindset and will never be a good SQL programmer
Hmmm...cant quite make the 1950's but would hope to aspire to becoming a good SQL programmer...:-)
one of our databases holds the customer telephone number as [TelNo] [nvarchar](15)...say for instance '01234567890'
I have to create daily call lists for our Sales Desk team.
Option 1 :
SELECT TelNo FROM TABLE
...result being 01234567890
Option 2 : SELECT LEFT(TelNo, 4) + ' ' + SUBSTRING(TelNo, 5, 3) + ' ' + SUBSTRING(TelNo, 9, 6) AS Telephone FROM TABLE
...result being '0123 456 890'
From an end users perspective, I trust you will agree, that the second option of '0123 456 890' is far easier to use
So my (naive) question and hopefully I wont be sent to hell for this......:crying:
Why should I have to configure the front end app to perform the "split" when SQL can already present it ....surely this puts more effort on the development of the front end app...which I also have to deliver?
My thinking here is that for most apps/reports..if they are to be at all useful...will only contain a small subset of the data and if it is necessary to reformat an output it can be done quickly in SQL rather than rebuilding the app......hey ho, off we go.
:hehe:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 1

For what it's worth, we started doing simple formatting like this within the database code as well for pretty much the same reason - it's less hassle to implement database changes then application builds.

That may not be the "best" reason, but a reason nonetheless. That's my 2 cents.

Cindy

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

Generally, the idea is to split responsibility between different part of the whole for efficiency and consistency. The user interface is where the data is actually being presented so that's the logical place to handle all aspects of presentation, including formatting.

Truth is, it doesn't really matter, especially in a smaller environment, so long as everyone who works on your system is on the same page. You really want to avoid doing it one way in some places and another way in others. If you do end up in a true N tier environment, you're likely to find all formatting done by the end user app.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Truth is, it doesn't really matter, especially in a smaller environment, so long as everyone who works on your system is on the same page. You really want to avoid doing it one way in some places and another way in others. If you do end up in a true N tier environment, you're likely to find all formatting done by the end user app.

Thanks...as we are unlikely to move to N tier then think we will continue as is for our users/apps...but appreciate the advice if things alter.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

RP_DBA SSCertifiable Points: 5116 More actions · Answer 4

There are exceptions, but we usually handle all formatting on the front-end.

Haven't seen anything this simple in practice but you get the idea.

SET NOCOUNT ON

DECLARE @temp TABLE (SomeValue NUMERIC(5,2))

INSERT INTO @temp (SomeValue) VALUES (10.25)

INSERT INTO @temp (SomeValue) VALUES (20.5)

INSERT INTO @temp (SomeValue) VALUES (30.75)

INSERT INTO @temp (SomeValue) VALUES (40.25)

INSERT INTO @temp (SomeValue) VALUES (50.5)

INSERT INTO @temp (SomeValue) VALUES (60.5)

SELECTSUM(CAST(SomeValue AS INT)) AS SomeValue

FROM@temp

SELECTCAST(SUM(SomeValue) AS INT) AS SomeValue

FROM@temp

SomeValue

-----------

210

SomeValue

-----------

212

_____________________________________________________________________
- Nate

@nate_hughes

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 5

It actually does matter a bit especially when it comes to formatting things like dates. SQL Server doesn't have much of chance of formatting dates correctly on a global basis where local settings on a PC do. Same goes for currency, I suppose.

The other idea behind this is that clock cycles spent on formatting can be distributed to the client instead of doing it on the SQL Server especially if the server is busy.

Of course, if you're formatting data to save in files, there's sometimes no GUI or external program to do the formatting with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

So far as the 1950's mind set goes... I use it all the time because sometimes the old stuff is the good stuff. Stop and think about it... the 1950' version of things had to be very, very resource conservative and very, very fast because the hardware back then, ummm... sucked. Imagine how fast it runs on good hardware. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

Jeff Moden (7/22/2010)
It actually does matter a bit especially when it comes to formatting things like dates. SQL Server doesn't have much of chance of formatting dates correctly on a global basis where local settings on a PC do. Same goes for currency, I suppose.
The other idea behind this is that clock cycles spent on formatting can be distributed to the client instead of doing it on the SQL Server especially if the server is busy.
Of course, if you're formatting data to save in files, there's sometimes no GUI or external program to do the formatting with.

I agree in some ways and while I generally would rather suggest to someone to doing things the "right way" even if they're on a small system, I think there is a certain extent to which these things don't matter. I would be willing to bet that the developer who is even aware of what 'localization' means is not the person who is ever going to be asking the questions about formatting here. So that's why I say it both ways ... there is a right way, but if you're not in an environment where it really matters then it's not something I would put very high up on the list.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 8

bteraberry (7/22/2010)
I would be willing to bet that the developer who is even aware of what 'localization' means is not the person who is ever going to be asking the questions about formatting here.

Now THAT's something I definitely agree with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)