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.

If you haven't posted your T-SQL Tuesday Blog yet, it's NOT too late.  Here is the original T-SQL Tuesday #22 invitation. 

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:

WITH DB_Buffer_Stats

AS
(
  SELECT
    database_id as database_id, 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
)
SELECT
  ROW_NUMBER() OVER(ORDER BY [Cached Size (MB)] DESC) AS row_num,
  database_name,
  [Cached Size (MB)],
  CAST([Cached Size (MB)] / SUM([Cached Size (MB)]) OVER() * 100
       AS DECIMAL(5, 2)) AS pct
FROM DB_Buffer_Stats
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
ORDER BY row_num;

The results will look similar to this:

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.

Don't forget to register for our exquisite upcoming event SQLPeople INSPIRE NYC, coming November 12, 2011.  AND, if you'd like to enter to win an iPad2, you can share your experiences and submit your story in the  SQL Server Performance Story Contest, 2011

You can follow me on Twitter|Pearlknows, and to take a look at our products and services, please visit us at Pearl Knowledge Solutions' website.