Hello Dear Reader, today is T-SQL Tuesday #22. I’ve been out of the T-SQL Tuesday game for a little while and wanted to jump back in. This edition is being brought to us by Robert Pearl (@PearlKnows| Blog ), so Robert what is the topic this month?
“Therefore, the topic of this month’s T-SQL Tuesday is, “data-presentation” Or put better, formatting data for presentation to the end-user.
We may be the developers, and techno-geeks behind the code, whether simple, advanced, spaghetti, or otherwise. But, the data the user sees is most critical. The query output, the report, or data presentation, must be absolutely formatted in such a way that is easily understandable and readable by the end-user. The end-user can be the boss, supervisor, department head, the analyst, employees, or customers. And they must be the ones we cater our queries to!
Therefore, I am inviting you all to write about “data presentation” to the user. This can be in the form of T-SQL code, an SSRS report, etc. What can you do to streamline data presentation? I used a CTE, you can use one, but you don’t have to. No hard format, just be creative, and mention the importance of data presentation. “
Data Presentation It IS!
IT’S ALL IN THE PRESENATION
I must confess I’m a bit of a snob when it comes to code. I will stop, reformat, and then read code if it is not formatted correctly. But I also believe that what you do every day becomes habit. And good coding standards are a habit that you want to get into. This isn’t cool, this isn’t sexy, it isn’t functional (your code will work poorly formatted), and it may take a little longer to type out.
“So Balls,” you say, “Why should I do this? If it will not benefit me?”
AH Dear Reader because easier isn’t necessarily the best way to go. This is one place where it is better to put in the extra work. Let’s just look at a very simple example. Let’s use some DMV’s to get an execution plan. *You would NOT want to execute this statement on a busy server. This would pull all active requests and their XML execution plans. *
SELECT * FROM sys.dm_exec_requests der CROSS APPLY sys.dm_exec_query_plan(der.plan_handle)
All of this can fit on one line. But should it? When we look at this we are doing a select star, pulling back everything on the server. When your doing a select *, in most cases, you are trying to figure out what rows you are trying to select. When you ship to production you should be trimming the columns that you do not need. We’ll get to that eventually but for now let’s do some formatting.
This is the way I write my code, no one way is right for everyone, so please take this with a grain of salt. First things first, let’s add some space.
SELECT
*
FROM
sys.dm_exec_requests der
CROSS APPLY
sys.dm_exec_query_plan(der.plan_handle)
I like my key words on separate lines, and I like indentation of values that I want to return and table names. Now when you read this, it is a lot easier to understand what it is that we are looking at. So let’s go a little farther and trim down the number of columns we are looking at. All we need is an execution plan.
SELECT
der.session_id
,deq.query_plan
FROM
sys.dm_exec_requests der
CROSS APPLY
sys.dm_exec_query_plan(der.plan_handle) deq
This is starting to shape up. If you execute the other query you will see that we are pulling back a lot of columns that we do not need. That is data coming over the wire.
So these have been simple, but let’s look at another coding example that uses a CTE. I use this in my demo’s for Compression. This takes a look at the index operational stats dmv and helps you determine the update and scan activity that are occurring on your tables. This is sizeable and complex, and if you didn’t have formatting it would be nigh unreadable.
WITH IndexUsage(tableName, IndexName, [Partition], IndexID, IndexType, USAGE, Percentage)
AS(
SELECT
(ss.name + '.' + so.name)
,si.name
,ios.partition_number
,ios.index_id
,si.type_desc AS indexType
,'UPDATES'
,(ios.range_scan_count *100.0/
(ios.range_scan_count +
ios.leaf_delete_count +
ios.leaf_insert_count +
ios.leaf_page_merge_count +
ios.leaf_update_count +
ios.singleton_lookup_count)) AS percentScan
FROM
sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
JOIN sys.objects so
ON so.object_id=ios.object_id
JOIN sys.indexes si
ON si.object_id = ios.object_id AND si.index_id = ios.index_id
JOIN sys.schemas ss
ON so.schema_id = ss.schema_id
WHERE
(
ios.range_scan_count +
ios.leaf_insert_count +
ios.leaf_delete_count +
ios.leaf_update_count +
ios.leaf_page_merge_count +
ios.singleton_lookup_count)!=0
AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1
UNION ALL
SELECT
(ss.name + '.'+ so.name)
,si.NAME
,ios.partition_number
,ios.index_id
,si.type_desc
,'SCANS'
,(ios.leaf_update_count *100/
(ios.range_scan_count +
ios.leaf_insert_count +
ios.leaf_delete_count +
ios.leaf_update_count +
ios.leaf_page_merge_count +
ios.singleton_lookup_count))
FROM
sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios
JOIN sys.objects so
ON so.object_id = ios.object_id
JOIN sys.indexes si
ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id
JOIN sys.schemas ss
ON so.schema_id=ss.SCHEMA_ID
WHERE
(
ios.range_scan_count +
ios.leaf_insert_count +
ios.leaf_delete_count +
ios.leaf_update_count +
ios.leaf_page_merge_count +
ios.singleton_lookup_count) !=0
AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1
)
SELECT
iu.tableName
,iu.IndexID
,iu.IndexType
,iu.USAGE
,iu.Percentage
FROM
IndexUsage iu
ORDER BY
iu.tableName
,iu.IndexName
,iu.Percentage DESC
WOW, it is a lot of fun to play with, but without formatting this would be a nightmare to read through. We’ve all had code that we had to read that gave us trouble. Think of the next DBA that has to come along and read it, and make things a little easier on them.
Hopefully the DBA before you did the same thing!
Thanks,
Brad
