Hey, everybody! Welcome to T-SQL Tuesday #22! Today's topic is Data Presentation, and the importance of formatting data to the end-user. Someone I know intimately is hosting today's blog party, and that would be me :-) - (Twitter|Pearlknows and Pearlknows Blog).

Just because I'm hosting, doesn't mean I shouldn't participate - in fact, I believe its warranted. I too am subject to all the rules, as put forth by our T-SQL Tuesday founder, SQL MVP, and master DJ of this blog party Adam Machanic. Thanks, again Adam for the opportunity!

I'm already seeing some great posts come in, and really appreciate their participation. Some really creative and interesting articles and code! The topic is wide open, so just let your creative juices flow.

As I was saying in the t-sql tuesday invitation post, it helps a lot, if we can simplify our code so we can therefore use it in our presentation layer.

For example, when doing comparative analysis of the dataset results returned by a query, it makes it completely understandable if the output includes a percentage column.While for the end-user, it may be hard to digest milliseconds, megabytes, totals, and other assorted aggregated data, everyone can easily comprehend when something is X% percentage out of the whole.

With the advent of Common Table Expressions (CTE), this makes it a whole lot easier to return all the data rows, along with the percentage in one single T-SQL pass.

Rather than get the initial results, declare and initialize variables, create/drop temporary tables, derive subsets and use sub queries, we can use CTE to streamline this operation, and reference the resulting table multiple times in the same statement.With percentages, it is easy to create data charts, such as bar and line graphs as well as pie charts.The visual eye-candy that all our end-users and higher-ups love! Here is my example:

First, with the following raw t-sql code, we can get memory buffer pool usage statistics by database, using the sys.dm_os_buffer_descriptors DMV, which returns information about all the data pages that are currently in the SQL Server buffer pool.

SELECT
DB_NAME(database_id) AS database_name,
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors AS DM_IO_Stats
GROUP BY database_id

But, if you want to add a percentage usage column, we can encapsulate the above T-SQL in a CTE, that effectively returns the percent of memory used by each database, respectively:

Therefore, if you simply take the data returned, specifically the database_name and pct columns, cut and paste into Excel, you can easily generate a pie chart, like this one below:

There are many ways to format data for the end-user, and mine is just one of many examples! You can see all the different ways and examples given to us by the participants of today's T-SQL Tuesday event linked in the comments section from my original post. Thanks again to all those contributions! I will have the T-SQL Tuesday Roundup featuring all of your posts in the coming week.