T-SQL Tuesday #36: What Does the SQL Community Mean to You (Me)?

TSQL Tuesday Logo

T-SQL Tuesday #36: How rad is Community? Rad enough for me to say “rad.”

I hate doing this, but I’m throwing this post together at the last second, as with PASS Summit going on last week, I completely spaced that this was T-SQL Tuesday Week. I blame the fact that I dropped my #TSQL2sDay search column out of TweetDeck last week, but that wouldn’t even have helped, because I spent most of my time on the Surface, but that’s a different story/post altogether. Community is something pretty important to me, so I’m here trying to get this out the door by the deadline (I failed, see below).

T-SQL Tuesday #36 is being hosted by Chris Yates (blog | @YatesSQL), who chose this Community-related topic this month. It’s pretty fitting, considering a good chunk of us have just gotten back from PASS Summit in sunny (yes, really) Seattle this past weekend, where there’s a lot of “community” going on.

Hard to Avoid a Summit Story

Having been one of those that just returned from Summit on Sunday, it’s pretty hard for me to think about this without thinking about last week. I had a couple different things I wanted to say, but I’ve settled on the following, about being a and helping Summit FirstTimers.

Last year at Summit was our first time there. We’ve both been to a fair number of Tech conferences, so it wasn’t all  a new experience for us. This, combined with the fact that we already “Twitter Knew” a fair chunk of people, led us to not opt-in to the organized First Timers networking event (I’m sorry, Tom). Even with the fog machine, rock music intro the FirstTimers had heading into 6ABCD (which was pretty bad-ass), we were OK with this.

We’ve learned a lot about our Community since our first Summit, only a year ago.

This year, Tammy signed up to be an Alumni Mentor for FirstTimers. I was added as kind of an “unofficial” mentor to help her out, instead of having a group of my own, because, when you get right down to it, I’m a huge pansy. I was going to be OK just being there, but not being a mentor myself. That’s scary!

First Timers Sign for Groups 55, 56, 57

Groups on our sign. My rogue group became 57A.

As it turned out, there were a lot more people show up to the FirstTimers networking event than expected. I was standing there with our group’s (and two others’) sign, directing people which table to sit at, depending on which group they were in. At one point, Buck Woody, the guy with the microphone, and therefore the most powerful person in the room (turns out Buck Woody with a microphone is the best, but scariest thing ever), just told everyone to sit down anywhere, because it was taking too long to get everyone in. Next thing I knew, the previously-empty table I was standing next to was full of eager first-timers, along with Tammy’s table, and the other two groups on our sign.

Ohhhhhhhhcrap, I now have my own group of FirstTimers!!!

I had to get over being a pansy real fast. It did help by leading off by telling everyone sitting at my table that I guarantee I was the most scared person siting there. The time we had to sit there and listen to speakers and talk amongst ourselves actually went pretty fast. My group didn’t talk amongst themselves quite as much as I maybe would have liked, but they did have some questions about the conference, which I could answer and help out with. Plus, my head didn’t explode!

Where am I going with this? To me, “SQL Community” is sitting and talking face-to-face with people I’ve never met before…even though doing that scares the living crap out of me. After this experience, I’m sorry that we didn’t do the FirstTimers event last year. I’m going to make up for that in the future, though, by going ahead and volunteering to be a mentor of my own FirstTimers group in future years.

Timezone Fail

Bonus section!

Soooo, this post is late. I forgot that we’re GMT –6 now, because we’ve gone back to Central Standard Time. When I started writing this, I was shooting for 7:00p local. Then, at about 5:59, I realize the truth. Even then, this machine is showing 7:03, so I still failed.

And I’m the guy always crabbing about people saying “EST” when they really mean “EDT” :-(

T-SQL Tuesday #30: A DBA’s Ethics

T-SQL Tuesday 30
#30: Deep T-SQL Tuesday or Deepest T-SQL Tuesday?

Chris Shaw (blog | @SQLShaw) brings us T-SQL Tuesday this month. His topic of choice is “A DBA’s Ethics”, which is a pretty deep subject! I feel like this post goes off the rails a little bit compared to the direction of the invitation post, but I had something happen last week that really made me think about this topic in a specific way.

Chris says, “Don’t consumers and business owners have to trust someone at some time with their data?” This is a line that I have clung to in the past; at some point, it has to come down to trust. Since I feel this to be true, it makes me somewhat crabby when regulations & whatnot seem to step in the way of that trust, possibly making it hard for someone like a DBA to do their job due to data or system access restrictions. I do believe that there are times and places for regulations like this, and after what I learned last week, I’m probably more on the side of these types of security regulations than I was before.

NPR to the Rescue

See, I wasn’t even sure if I was going to write a post this month or not. I wasn’t sure what I wanted to talk about or if I was even going to be able to get enough coherent thoughts together. But then, one day last week, I was minding my own business (more or less), driving home from work, listening to WPLN, as I am wont to do, when this story came on. To very roughly summarize for those not interested in spending 18:34 listening (I do recommend listening to or reading the story):

  • Father is upset as his son is convicted of bank fraud.
  • Asks younger son to promise to never get into that kind of trouble.
  • Younger son promises.
  • Younger son starts own business.
  • Realizes one day company is under water.
  • One thing leads to another and 22 years after The Promise, said younger son is convicted of same crime as older brother.

I was interested in where this story would go… I mean, why do otherwise “good” people do “bad” things? At the beginning, I thought that somehow research was going to say that doing bad things “runs in the family” (the older/younger brother angle). As the story progressed into the part where Toby Groves begins asking employees to help, and they agreed, I started to think about how there’s no way I would do such a thing…and how could these people?

When the story got to its punch line, it made me think about how this topic related to DBAs:

We like to help each other, especially people we identify with. And when we are helping people, we really don’t see what we are doing as unethical.

A couple questions developed in my head when I heard it, and as I thought about it more, the questions only got more complex:

  • Are we, as DBAs (or DBA-type folks) at all immune to this type of thinking?
  • Would a DBA be more willing to help another DBA specifically? If so, how thin can we split that hair?
  • What about supplying data for audits? Does that change the thought process?
  • Does having an ethics statement really do any good?

I don’t have answers to these questions—not sure anybody does—but they make for interesting things to think about.

If Only I Had a Psychology Degree

I know the first question I have listed there seems pretty pretentious, but I wound up there due to specific reasons. Of course DBAs like to help other people; that’s not what I’m saying. What I am saying is weighing “helping people” while still operating within “the rules” is kind of what we do. I’ve spent a fair amount of time working directly with developers both on projects and to get projects deployed to Production systems. Although it is nice to be able to tell coworkers “that’s all OK”, that isn’t what we get paid to do; instead, more often than not, we’re paid to say No. Not because we don’t like people or enjoy it, it’s because we are trusted (there that is again) to apply “the rules” objectively to everything we are involved in or are supposed to approve for deployment. These “rules” can be administrative procedures, architectural/design policies, or even something simple like scheduling. Because of the nature of what it is we do, we are used to taking a step back, away from the people involved, to ensure that we do the right thing.

Does making decisions like that on a day-to-day basis make a person able to apply that same objectivity to every situation they ever encounter? Who knows? What about if the situation was changed a bit? DBAs (and sysadmins, for that matter), tend to rag on developers a little bit; usually in a good-natured way. But, I believe that, for the most part, DBAs and Developers are fundamentally two different kinds of people—we tend to think and act differently (and that’s OK). So, what happens if a fellow DBA comes to us with a problem or the results of a mistake and are looking for a way to fix it quietly or sweep it under the rug? Since we identify with the fellow DBA more so than, say, a developer, are we more likely to go along with the cowboy fix, as is pointed to by the study in the NPR story? I mean, if one doesn’t truly stop and think about what is going and what is being asked of them, it’s easy to simply think, “oh, Jim-Bob’s a DBA over on the Production team, surely he wouldn’t want to do anything outside of policy…”

What about the audit situation? This can go both ways: on one hand, one wants to help the auditor do their job by getting them the data they’re asking for. On the other hand, you may be wanting to help your team (or yourself!) out by possibly not supplying everything involved in a request. It’s easy to sit here and office chair quarterback the scenario, but that’s the point—it’s easy when you’re not in the situation  yourself, in the same mindset (or lack thereof), staring the same consequences in the face. Thoughts and actions only count when…well…when they actually count.

This is where doubts about ethics statements begin to creep into my head. It’s easy to read through SANS’s Code of Ethics that Chris pointed out on this fine Tuesday and laugh, being like, “yeah, like I’d ever knowingly hurt someone’s reputation or look up the boss’s salary in the HR DB.” The problem is that opportunity or request might not come up on a Tuesday morning while we’re sitting around reading blog posts over our second cup of coffee—it might come up while you’re having an exceptionally bad day, and all you want to do is make at least one person happy. Before you know it, you might have gone off and done something illegal, or at the very least, ethically questionable.

Does having a company, community, or personal code of ethics keep this from happening? The way I interpret the reported study’s results, I would have to say no. Of course, that’s not true for everyone or every situation—it depends! It depends on a lot of things. So many things, in fact, that it’s completely within the realm of possibility that having such a code and reading it regularly to remind ourselves of the covered items could just cause the right synapse to fire in the right situation to keep us from doing something stupid. Does that make it worth the effort? Probably.

I have to say, though, historically, I’ve gotten crabby about things like this that get formalized. I always ask “Really?!”, because I feel like I’m pretty hard-wired in this department and get a little offended by having a code of ethics thrust upon me. I have to say that I’m a a little afraid after hearing the NPR story that there’s a chance I would let things get out of control before I realized what’s going on. At the same time, though, I realize that having that fear and being alert to it is probably about the best way to safeguard against doing unethical things in the first place.

But…

There’s still a problem here. The problem is that we’re not perfect. Insidious things creep in at the corners without us noticing. Even though we’re trained and practiced in recognizing when something abnormal is going on and to see through the glossy surface down to the gritty details, things can still go horribly wrong due to being stuck in those details and missing the big picture.

I don’t know about you guys, but that NPR story freaks me out a little bit. Do I believe what I said above, about DBAs being used to making similar decisions, makes us immune to this type of “sympathy think” (pardon me while I make up words again)? No! I’m worried I go around every day a hair’s breadth away from starting something highly unfortunate just because I wasn’t paying enough attention or thinking the right way about a task.

Sure, talking about a DBA’s ethics is a little bit different than talking about mortgage fraud, but the fundamental decision is the same: Could what I am about to do hurt myself or someone else? It’s hard to think about that question at every turn throughout the course of the day, but apparently we need to, because once again, our animal brains are being a pain in the butt.

T-SQL Tuesday #28: Jack of All Trades Crew, Checking In

T-SQL Tuesday—always a good option for helping to get back on the blogging horse.

TSQL Tuesday #28
T-SQL Tuesday #28

This month is hosted by Argenis Fernandez (blog | @DBArgenis), a SQL Server MCM & a #SQLFamily member that I have yet to meet. His topic of choice is “Jack of All Trades, Master of None?”, which is right up my alley, because for pretty much my entire IT career, that phrase has described me. It still does, right this second, but I’m trying to get over that. More on that coming.

Because I should be able to use them to frame out a good story (and because I’m cheap), I’m going to work with the list of questions Argenis has in his invitation post.

Are you specialized? On something? Or anything at all?

Am I anything at all? No, not really, thanks for asking! :-D

Anyway… it’s not really safe or fair to answer whether or not I’m specialized with anything but a firm “no.” I’ve been this way from day one. I don’t know that it’s been a conscious decision to get to this point more than it just happened as a result of being driven by a desire to know how everything works. Sometimes that leads to depth in weird places, which can come in handy while watching Jeopardy! on TV.

My non-specialization situation at the moment includes the capabilities of a decent SQL Server DBA, being what I’ll call “serviceable” when it comes to data modeling, and could still be a sysadmin if push came to shove, in a day job in which I have become the go-to ETL Developer. It’s a little weird, I admit. But, all of those other things help with the current focus. The ETL job is slow, you say? Well, is the server on the floor? Is the latency over the WAN link 700 ms per round trip? All of my other skills help, and that is what I think is the best part of being a jack-of-all-trades: It’s possible to know just enough to answer a lot of your own questions!

I’ve said it before, but another thing I like about knowing a little bit about a lot is you can make friends/commiserate with almost everyone in the IT department.

Are you the SQL Guy at work? Or the one who does everything?

Due to the size of company that I work for, there are very few “Guys” at work—everyone has a specific job (or sometimes jobs) that they do. Basically…near-insect-grade specialization. There’s not room for a jack-of-all trades at larger organizations, in my experience, with the possible exception of smaller, autonomous groups.

Having spent time in one of those smaller, autonomous groups within a larger organization, I was a little bit of the guy who does everything. My whole team was, actually. We were “Windows System Admins”, who ran just about everything except Exchange and the MSFT monitoring platform du jour, plus Citrix to boot. Although each of us had our strong points, pretty much all of us could get through whatever needed to be done and have things work when we were done. I think that just goes along with being a “sysadmin”—being a jack-of-all-trades is almost a necessity. Need to know hardware? Check. Security theory AND implementation? Yep. IIS (Apache as necessary)? Probably. Networking? You betcha. SQL was just one of the things that I did while there, although I did do a lot of it.

Do you code? And configure wireless routers at work also?

Hell no. I mean… not if I can help it. See, when I started in IT for real, I knew one whole programming language: Visual Basic 6. Two classes in school on it, and that was it. I wrote a little print queue viewer/management app while a student (hey, it was deployed on 2000+ machines!), but no real experience. To this day, VB6 is the only real (“real”) programming language I know. Not having a strong coding background does cause some problems occasionally, especially when talking to Developers who are used to DBAs coming up through those ranks. I definitely don’t know much about software engineering theory, and that’s where it shows up the most.

As part of the afore-mentioned sysadmin gig, I wrote a command-line only VB app as an automated interface between a couple of systems, but I’m not exactly proud of that moment, for a number of reasons. The one that applies here is one of the downfalls of being a jack-of-all-trades: it’s easy to cowboy up and do quick-and-dirty things off to the side, because you can. Perhaps even more dangerous: because no-one else can.What happens as soon as you’re done? Well, if you’re not careful, it winds up in Production, and then it becomes a support nightmare; if not for you, it will be for a coworker or the next guy. Either of which may some day hate you when their phone rings at 0300 because the wrong piece of duct tape fell off your masterpiece. I think being a jack-of-all-trades can be a good thing, as long as one of those trades is holding onto whatever processes and standards are in place…and if there aren’t any of those, hopefully one of said trades is coming up with some good organizational processes and standards!

As for the wireless router configuration bit—I try to keep that at home. Pretty sure the network guys wouldn’t like me messing around with those things. Just because I [used to] know Cisco IOS, doesn’t mean I should use it. That brings us to another good specific skill that a jack-of-all-trades should have: Knowing when to sit quietly. This goes for both wireless routers and writing anything in VB6 that has a prayer of ever seeing real, actual production use.

If you had to pick one thing to specialize on, what would it be?

Yeah, about that… All the above said, I actually am going to try to specialize on something. Of course, it isn’t enough to say I’m going to specialize on SQL Server. There’s too much in the product now. I’m going back to the thing that got me truly interested in the prospects of becoming a Data Pro in the first place: Business Intelligence. Unfortunately, I don’t think it’s safe to make that a goal, either. Just the BI side of the SQL Server platform is becoming too broad and too feature-rich to come to grips with. I’m going to have to be content with possibly not knowing anything at all about Reporting Services to focus on what I really want to do: Analysis Services. I actually want to be able to do most of the architecture work surrounding big BI projects, from start to finish (except for SSRS!), but I’m afraid that even just SSAS, including all of its new related technologies, could turn out to be too much.

That, though, is a journey that I hope we can all share in. Because I’m nice like that.

Other Thoughts

Being mechanically wired more than anything, it’s not quite as easy for me to tear down a piece of T-SQL as it is, say, the battery operated toys I used to take apart…Or a carburetor. But thanks to the Internet, it’s easy for me to read about and learn from someone who is more adept at doing that sort of thing. This shows two more helpful skills for a jack-of-all-trades: Being able to read and learn is a really important one, and being able (and willing) to share back out is another good one. You never what kind of DBA trying to configure Exchange you’re going to help out.

T-SQL Tuesday #24: Prox ‘n’ Funx

Procedures and Functions. Well, this could be interesting. Everyone else’s posts, that is.

T-SQL Tuesday

#24, brought to us by Brad Schulz

OK, T-SQL Tuesday Twenty-Four: Two-year anniversary edition! Pretty sweet of Adam Machanic (blog | @AdamMachanic) to come up with this whole thing two years ago. A good guy, he is. This month’s topic is Prox ‘n’ Funx, brought to all of us by Brad Schulz (blog). I kind of feel bad here—I don’t really know much about Brad, but he’s an MVP, and flipping around his blog brings up some pretty cool posts. I really like this one, for example.

I actually have what I think is a decent little thing to talk about for this topic. It could have been a stretch topic for T-SQL Tuesday #11 about misconceptions, even if [I hope] not exactly a widespread one. This topic revolves around transaction control and error handling within stored procedures, which can be a crazy topic in and of itself. Books could be written on the topic. Specifically, the conversation that I found myself involved in one day was about what happens to any open explicit transactions when a procedure runs into an error.

Let’s Talk About Procs Dying

Once upon a time, someone said to me that if an error occurs within a procedure which contains an explicit transaction, that transaction will remain open, potentially blocking other sessions on the system. In short, that’s not true, and it’s fairly easy to prove. We’ll work through a quick little script to do this, and will include modern TRY…CATCH error handling, because that will come in when we get to the main point (I say “modern” there like it’s a new thing, but coming from a guy who has worked with SQL 2000 more than anything else, that distinction feels necessary). It actually doesn’t matter if there is any error handling during the first part of this exercise, as the results will be the same. This is a pretty contrived setup, but that’s pretty much what I’m going for.

First, create a table & put a couple of rows into it. The two columns we’re most worried about are “FakeNumberColumn” and “ActualNumberColumn” (note their data types), so named because the error which will be triggered in a little bit will be a type conversion error.
-- Create Table
CREATE TABLE dbo.TransactionTest2008
      
(     TransactionTestID       INT                     NOT NULL    IDENTITY(1,1),
            
RowDescription          VARCHAR(50)             NOT NULL,
            
FakeNumberColumn        VARCHAR(10)             NOT NULL,
            
ActualNumberColumn      INT                     NULL
      )
;

-- Populate it with a couple of rows
INSERT INTO dbo.TransactionTest2008 (RowDescription, FakeNumberColumn)
      
SELECT 'Actually a Number 1', 10
            
UNION ALL
      
SELECT 'Actually a Number 2', 100
;

Now for the really contrived part: a Stored Procedure that will throw an error if FakeNumberColumn contains something that won’t implicitly convert to a numeric:

CREATE PROCEDURE TransactionTester2008
AS

BEGIN try
  
  
BEGIN TRAN
     UPDATE
dbo.TransactionTest2008
          
SET ActualNumberColumn = FakeNumberColumn * 2
     
--   select *
-- from dbo.TransactionTest2008

     -- Wait for 10 seconds so we have a chance to look at DBCC OPENTRAN (unless of course it dies first)
     WAITFOR delay '00:00:10'

   COMMIT TRAN
END
try

BEGIN catch
-- Some kind of error has occured
  
PRINT 'Welcome to Catchville'

   IF @@TRANCOUNT > 0
      
ROLLBACK

   -- Raise an error with the details of the exception
  
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT
   SELECT
@ErrMsg = ERROR_MESSAGE(),
      
@ErrSeverity = ERROR_SEVERITY()

   RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH


(The commented-out SELECT statement can be un-commented if you like, to see the state of the table at that point of execution.)

As it is now, the proc will run successfully. The 10 second WAITFOR in it gives you time to run DBCC OPENTRAN in another query window to see the proc’s open transaction.

EXEC TransactionTester2008

Open TransactionNow we’ll make things somewhat interesting. Insert another row into our table to put a letter into FakeNumberColumn, then run the proc again.

INSERT INTO dbo.TransactionTest2008 (RowDescription, FakeNumberColumn)
     
SELECT 'Not really a Number', 'F'

EXEC TransactionTester2008

Things won’t go so well this time…

Bombed Proc RunWe get the PRINT message about being in Catchville, so we know that our exception was caught and execution finished in the CATCH block. At this point, go run DBCC OPENTRAN again, and you will see that there isn’t a transaction open. This would be the expected behavior. No transactions are left open; the in-process activities are rolled back.

I should also note that a less-severe error, such as a constraint violation on an INSERT, will only cause an error in that particular statement. The Engine will skip over that statement & continue processing normally. That behavior has led to some near-brown pants moments while running a huge pile of INSERTs, putting in some provided business data, but that’s what explicit transactions are for!

Now, About Timeouts…

OK, that section wound up pretty long. Here’s where I’m actually getting to what I want to talk about…

We’ve established that errors in Stored Procedures will not lead to transactions being left open under normal circumstances. There is a situation where things don’t go so well: when a client/application connection times out for one reason or another. If this happens, the client side will close its end of the connection, and after the in-progress query SQL Server is running completes, nothing else really happens. This can leave open transactions, which, of course, are bad, bad, bad.

Starting with where we left off above, we can simulate an application timeout by cancelling the running SP in Management Studio.

First, delete the error-producing row from dbo.TransactionTest2008:

DELETE FROM dbo.TransactionTest2008
  
WHERE RowDescription = 'Not really a Number'

Execute TransactionTester2008 again, and this time, while in the 10-second WAITFOR, cancel the query in Management Studio. Even with the TRY…CATCH block in place, the explicit transaction is left open (check with DBCC OPENTRAN). What this means is that whatever application (or DBA!) running the statement(s) is responsible for closing an open transaction if a session times out or is cancelled. In my experience, if one is in the habit of wrapping everything you do in explicit BEGIN/COMMIT/ROLLBACK TRAN, they’ll be less likely to cancel a script they’re running and then sit there blocking half of the rest of the world. Not that I’ve been there, or anything…

There is a safety net here: XACT_ABORT. I first learned about XACT_ABORT while doing some work with Linked Servers a few years ago. What XACT_ABORT does when set to ON is to force SQL Server to terminate and roll back the entire batch if any error occurs. Here’s the Books On Line page for XACT_ABORT.

In our case, flipping that setting to ON within our test SP will change what happens when a query timeout (or cancel) happens. Add “SET XACT_ABORT ON” at the begging of the above SP and re-create it thusly (either drop and recreate or add the line and change it to ALTER PROCEDURE):

CREATE PROCEDURE TransactionTester2008
AS

SET XACT_ABORT ON
[…]

Run the SP as before, and again, while in the 10-second WAITFOR, cancel the query. Now if checking for an open transaction, there won’t be one—it was rolled back by the engine when the timeout (cancel) occurred, because of XACT_ABORT. No locks are still being held, no other sessions will be blocked by the timed-out session. Smooth sailing :)

As Usual, Be Careful

Before hauling off and adding this to a bunch of SPs, beware the Law of Unintended Consequences. Test extensively, because no matter how self-contained a fix may seem, who knows what else may be affected. This is no where this is as true as it is in old, overly-complicated, poorly-understood legacy systems. I know DBAs like to fix problems and prevent problems from happening in the first place, but please make sure that no new issues are introduced while trying to “fix” things.

Oh; don’t forget to clean up after yourself!

DROP TABLE dbo.TransactionTest2008
DROP PROCEDURE TransactionTester2008

 

T-SQL Tuesday #22: Data Presentation

TSQL Tuesday Logo

Robert Pearl hosts No.22

It seems like it hasn’t been that long since last month’s T-SQL Tuesday post; I suppose time flies when you’re having fun and trying to finish up the same ETL project you’ve been working on since March.

This month’s SQL blog party is being hosted by Robert Pearl (blog| @PearlKnows), on the topic of “Data Presentation.” This is a good topic for me at this point, as I’ve all but finished my transition from DBA to BI Monkey (that’s something else I need to write about…). I think Robert is looking for specific examples of ways to present data, but since, as usual, I don’t have anything specific that I can actually publish, I’m left to speak generally about the topic.

Data Presentation: Just as Important as the Data Itself

In a previous life, I was responsible for almost everything data-related for the systems that we ran. As a result, I would get a lot of requests for data. One of my favorite requests would come in the form of, “can you give me some numbers for <X system>?” I would try to keep my response at least marginally non-snarky, but it would generally include two questions:

  1. What, exact, “numbers” do you want? (this is especially where I would have snark problems)
  2. What do you want the data to look like?

Of course, the first one is an important question—if the requestor cannot articulate what it is they actually want (or even what question they’re trying to answer), little else is going to matter. I’ll not dwell on this particular item too much, but suffice to say, sometimes getting a good answer to this seemingly easy question is anything but. I’ve basically come to the conclusion that this is normal.

Once over that hurdle, the conversation can move on to the presentation of whatever data/”numbers” it is the requestor wants. There are almost as many options for presenting data as there are for way to write the T-SQL to retrieve it. Just like writing the SQL in a way that is performance- and resource-conscious, care should be taken when working on the presentation design. It is imperative for the data to be presented in such a way that is understandable and digestible by its intended audience.

Notice I didn’t say “digestible by the party asking for it.” Don’t forget that the request originator may not be the party who is ultimately going to be parsing the provided data. If the audience is not clear in the original request, add a third question to the two that I have listed above: “Who is going to be acting on this data?”

Options for What Happens Next

When the “What do you want it to look like” question is asked, chances are decent that you’ve an idea about what the answer is going to be. If this is a one-off, ad-hoc request, Excel is a popular option. Alternatively, if a robust reporting system is in place, or this request will be a recurring one, developing a report to present the data might be a stronger choice. There are of course other options: the data could be destined for a statistical analysis application, where a CSV file would be more suitable. I would consider this an outlier, though—most of the time, data is prepared for direct human consumption.

Excel is such a popular option that you could almost call it Data’s Universal Distribution Engine (DUDE). Sending data over in Excel is less about the “make it pretty” side of good presentation as it is the “make it useful” side. I’ve found that Excel is a choice a lot of the time because the requestor wants to do more manipulation of the data once they get it. I’ll leave whether or not that is a good thing to the side; the truth is, such activity happens all the time. As a result, when preparing data for an Excel sheet, I like to have an idea of what the user is going to do with it. This sometimes helps to determine what data the user is looking for (if they don’t have a clear idea) but also can help with some formatting or “extras” to include. These “extras” could take the form of running subtotals, percent changes for Year over Year situations, or anything else that is easier to add via SQL instead of someone having to putz around in Excel.

Writing a report to present data has a different set of opportunities than pasting data into Excel. One of the things that I like to see in a solid reporting environment is a set of standards that apply to the reports themselves. Things like common header contents (report name, date/time stamp, name of the data source/DB the data is from, etc), standard text formatting, a common set of descriptors, etc, etc. In addition to making individual reports easier to read & feel more familiar, it can make it easier to compare data etween reports the hard way (one on each monitor), if one has to.

It's only worth 1,000 words if the first ones that come to mind are work safe

One thing each of these two tools gives you is the ability to present data in the form of pretty pictures. There’s a time and a place for everything, but the old cliché, “a picture is worth a thousand words” can/does apply. Sometimes it’s just flat-out hard to beat a good trendline. I have a much easier time seeing even the simplest of trends when data’s plotted out in a histogram. Conversely, one of my coworkers can look at a pile of numbers, not even sorted chronologically, and tell you what is going on in about three seconds.

Knowing where to put your effort goes back to knowing who your intended audience is. Likewise, knowing when to say “no” to visualization is a terribly useful skill. Every data element on the chart should be discernable, or else it doesn’t convey the information it is supposed to, and now the visualization is working against itself. The pie chart to the right? Don’t do that.

Summary

That’s about all I’ve got. In short: Presentation is important. Unfortunately, it can also be complicated. It’s important to ask questions early on in the process and to know your audience. Standardize if you can; help out a little with the complicated work if it can be done in SQL. Also, add visual representations without going overboard. I’ve always found turning “data” into “information” for people to be fun; if it can make someone else’s job easier/more fun, too, then all for the better.