Skip to content

Data Presentation – T-SQL Tuesday #22

September 13, 2011

TSQL2sDay150x150It’s T-SQL Tuesday again, with our host this month being Robert Pearl, of Pearl Knowledge Solutions. He chose the topic of Data Presentation for September.

If you haven’t participated in a T-SQL Tuesday, it’s easy. Read the rules in Robert’s post and read the basic idea in Adam Machanic’s original invittation. Then write a blog post with the logo to the right in it and link it back to Robert’s post.

That’s it.

Formatting Data

One of the things that DBAs and data professionals should learn is that formatting typically doesn’t belong in the database. The client, front end, or the tools used to extract the data should handle formatting, presenting the data as the client wants to see it.

When you store numbers, you don’t want to store things like currency symbols. So in SQL Server I should see this:

datapresent1

and not this:

datapresent2

The former gives more flexibility, and the latter limits what you display (and requires character storage).

The same thing goes for other data, for example, phone numbers. We don’t want to store our phone numbers like this:

datapresent3

If someone wants to see just the number, or they add in another format of number (say European), then you have inconsistencies. Also, it becomes harder to separate out issues. When you do separate out the number from the area code, how do you handle things if you have this:

datapresent4

That’s hard to handle. If all the phone numbers were numbers, it’s easier to handle. This allows one easy CASE statement based on length.

datapresent5

Or better yet, use better design and let the front end handle data presentation.

datapresent6

Doing It Right

I guess I haven’t talked data presentation so much as how not to handle data presentation. I know that SQL Server can do things like ordering, formatting, combining or splitting strings, or more, but ultimately I think that’s not the right way to handle things.

I’ve always viewed the database as the single bottleneck. It’s incredibly hard, and expensive, to scale a database server, while it’s easier to scale web servers, app servers, and client tools, and much less expensive. It’s even easier to scale developers and have them write more code to handle presentation on the front end instead of using SQL Server to do the work. I’d try to always push any presentation work to the client instead of the database server, just because of the workload and bottleneck on the server.

It might seem like more work up front, and it will be, but it will be infinitely better than trying to re-write code or upgrade your database server later when the load becomes larger.

About these ads

From → Blog

One Comment

Trackbacks & Pingbacks

  1. T-SQL Tuesday #22 Round-Up - Data Presentation - Robert Pearl's Blog

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,773 other followers

%d bloggers like this: