Home » sql server »sqlserverpedia-syndication »SSC » Currently Reading:

T-SQL Tuesday: Resolutions

January 10, 2011 sql server, sqlserverpedia-syndication, SSC 3 Comments

In my invitation to T-SQL Tuesday #014, I picked the topic “Resolutions”, clearly to play on the new year.  The only thing I have right now that could count as a New Year’s resolution is the RTFM365 project…it’s certainly going to take some stick-to-it-iveness to finish out a year of reading from BOL every day. The rest of what I have are just ongoing life goals. So why did I pick “Resolutions”, then, if I have nothing to say?

The invitation blog gave a few brief suggestions of  what to write about, but the interesting thing is how many different ways you can run with the word. When I looked at the Merriam-Webster entry for resolution, a few of the definitions stuck out as a sort of common theme:

1) the act or process of resolving: as

  • a : the act of analyzing a complex notion into simpler ones …
  • e : the separating of a chemical compound or mixture into its constituents
  • f (1) : the division of a prosodic element into its component parts (2) : the substitution in Greek or Latin prosody of two short syllables for a long syllable g : the analysis of a vector into two or more vectors of which it is the sum
  • …[and a bunch more.]

This complex circuit is made up of simple parts, and humor

Analyzing a complex notion into simpler ones. Hmm, why does that sound familiar? Familiar in a kind of SQL Servery way….

Databases

“Hey, let’s throw together a database to track our sales conferences!”  Uh, sure boss…that simple statement has the mass and temperament of 400 angry gorillas at a monster truck rally. In an earthquake. 

My job is now to take this complex idea and break it down into its simpler pieces – the data we need to store, the rules that hold the data together, the procedures that we need to run on it.  Once that’s done, everything should be oh so simple!

But of course, it’s not.

Queries

“Hey, can you get me a list of the people that’ve attended any of the conferences in the last three years? Not counting the webconferences. Or HR personnel. And we’ll need their company and contact info.” 

Again, with the “simple”-complex requests.  And again, I can’t necessarily just sit down and bang out a query. Depending on the database architecture and the request, I might need to start with two or three queries to get different sets of data, and figure out how to link them together:

  • I’ll get the set of all conferences in the last three years. If I can, I’ll limit that to non-webconferences here…alternately, I can get a list of webconference IDs and do a
    SELECT conference
    WHERE ID NOT IN (SELECT ID from Webconferences)….

    (That’s fairly lousy pseudocode, but I’m assuming you’ll go with me on this.)
  • I’ll also pull a list of individuals (with related info) and filter based on their job category.
  • Aaaaand then link these together.

There. Database: designed. Ability to break a request into logical chunks: achieved. Now it’s easy street!

Oh, ya think so, do ya?

Reports

“Okay, Jane.” [My name's Jen, sir.] “Could you get us a report for Bob? He needs numbers on attendance over time, categories, that kind of thing.”

Well, first of all, it’s time to go talk to Bob. What will he be using this for? Is this something he’s going to sort and calculate on himself, or feed into something else? Well then, Excel or CSV. Something presentable? Well then, PDF or Word.

And what questions is he trying to answer with this report? That’ll really drive the data and the presentation.  In the end, we might have broken this down to something like this:

  • Needs something in a handout format
  • Prefers graphics – pie and line charts – over lists of numbers.
  • Wants attendance by company (not individual) over time – that is, how many events attended per company per month, over the last five years.

Etc etc.

What this all boils down to is something I’ve heard Itzik Ben-Gan (blog) say more than once: a given problem breaks down into smaller, simpler, often reusable parts. And now we’ve learned a word for that, boys and girls: Resolution!

Happy T-SQL Tuesday!
Jen McCown
http://www.MidnightDBA.com/Jen

Currently there are "3 comments" on this Article:

  1. I see a cube yearning to be created to help answer those questions!

  2. [...] fact, if you like, you can even throw down a few resolutions for the new year (see Jen McCown’s #14).  And if this is your first T-SQL Tuesday, just pick a topic and jump [...]

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 1 day 2 hours 40 minutes 53 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/