Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

The largest free SQL Server community
Browse by Tags · View All
Adam Machanic 5
SQL server 5
tsql tuesday 4
data presentation 3
#SQL SERVER 3
Thomas LaRock 2
Brian Moran 2
Matt Velic 2
#SQLServer 2
tsql tuesday #22 2

Archive · View All
September 2011 9
April 2013 5
November 2012 4
July 2012 4
May 2012 4
August 2011 4
July 2011 4
June 2012 3
June 2011 3
March 2012 3

Robert Pearl's Blog

T-SQL Tuesday #22 - Data Presentation - My Example with CTE

Sep 13 2011 12:00AM by Robert Pearl   

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.


Republished from Latest Blog Posts - Pearl Knows - SQLServerCentral [39 clicks].  Read the original version here [32134 clicks].

Robert Pearl
126 · 1% · 402
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]