On Epiphanies, Friends, and Career Direction

On Monday, May 25, I woke up to a realization.  I don’t like my job!  I’d like to say it was startling or sudden, but that’s just not true.  But the epiphany wasn’t that I was unhappy doing the work I was doing, it was that I suddenly realized that I could not do it anymore.  Not wouldn’t… couldn’t.  Some switch flipped and I knew with absolute certainty that I wasn’t going to create enough change in my environment to make myself love my job. 

So, while I drove into work, I thought about my epiphany.  I asked myself questions: 

1) Why don’t I like my job? 
2) What am I going to do about it?
3) Am I losing my mind; have I become irrational? 

The answers, respectively, were: 

1) I don’t love Business Intelligence.  It’s a great field, and I thought it was the natural progression of my career, but I just don’t love it.  I am surrounded by people that do, and that just highlights the fact that I’m not passionate about BI the way they are.  That, coupled with the fact that I’ve been in a very tough consulting environment for a few months where I regularly do work that is neither fulfilling nor educational made for a bad combination. 
2) I’m going to change something. 
3) Surprisingly, no. 

Okay, so what to do?  I e-mailed a few trusted SQL friends and told them about my epiphany.  I know that every one of them probably said, “duh, Audrey” as they read the e-mail.  They all intuitively knew that I wasn’t doing something I loved and were all supportive and encouraging of my decision to figure out what to do next.  My dear and always supportive friend Aaron Nelson (Blog | Twitter) pointed out that his company was interviewing candidates for a database developer position over the next few days.  Now, I’ve known Aaron for a while, and he loves his job.  I’ve met his boss and co-worker, and they’re cool guys.  AND… one of my very best friends in the whole world, Ted Hughes, is an application developer over there.  (Tangent:  I know what you’re thinking… I’m friends with an application developer?  I am.  He’s brilliant and awesome, and if I ever need to hide a body, he’s the guy I’m calling.  Partly because I know that he has a truck and proper tools, but also because I know he’d show up. Oh, and he writes good SQL.  That’s how we became friends in the first place.  :End Tangent)

Also, my poor husband got this text from me on Monday:  “Had an epiphany. Don’t love BI. Getting a new job.”  To his credit, he just said, “Do whatever you need to do to be happy”.  Yay, Jeremy.

Long story short, epiphany on Monday, interviewed, loved what I heard about the position, offered position, accepted position, turned in notice on Wednesday.  My last day at Key2 Consulting is June 14.  My first day at ista is June 20.  Yes, I’m taking 3 days off.  Mike, my new boss, will appreciate me not being a frazzled, stressed, overwhelmed mess on my first day.  I will probably hang on to 1 or 2 of those characteristics even with the time off, but I haven’t decided which yet. 

Key2 Consulting is the company I’m leaving.  I love this company.  I pursued Key2 when I started to look at a career in BI Consulting.  Brian Thomas is an amazing boss, and more importantly, an amazing person.  I have friends here that I’m sorry to leave.  I have no regrets about coming to work at Key2, and I’ve truly enjoyed being a part of this family.  But, I’m moving in one direction and Key2 is moving in another.  Loving the people you work with is important, but loving the work you’re doing is more so.  For anyone who IS passionate about BI, this is a quality group of people. 

I know what you’re thinking:  Okay Audrey, you got a new job.  Big whoop.  What’s your point?  I do have a point (actually a few).

What I’ve learned about my career and life in general

1) Don’t be afraid to try new directions.  Also don’t be afraid to admit when they haven’t panned out. 
2) If you’re unhappy where you’re at, try to change where you’re at.  If you can’t do that, find a new place to be.
3) Friends are so important.  My friends validated and supported what appeared on the surface to be an impulsive decision.  But, they’d listened to me over the past few months, and each one of them knew exactly what to say.  A couple of them even had opportunites to offer up. 
4) Forget career ladders and job titles.  Figure out what aspect of this crazy career choice of ours you love and go get paid to do it. 
5) Sometimes you just KNOW.  When you do, ACT.  This doesn’t just apply to our careers.  This applies to life in general.  How often do we feel certain about what to do next?  When one of those rare moments comes along, go with it.

To my friends who read my rambly, strange e-mail that Monday and responded with kindness and encouragement… Thank you.  You know who you are, and I hope you all know that you’re awesome.  Hugs all around. 

–Audrey

Cage Match I: Anti-Joins

Before we get started on the Cage Match, a quick shout-out to my blogging partner and fellow Datachix, Julie Smith (Blog | Twitter).  She’ll be speaking at SQL Rally (May 11-13).  I’ve seen her slides, and man, are you guys in for a treat.  Attend her session for some SSIS and Datachix-y Awesomeness!

 On to your regularly scheduled blog post…

One of the coolest things about writing and presenting is every time I present, I end up learning something new. Either someone in the audience points out something I hadn’t thought of, or a question spawns a whole new line of thinking. Both scenarios are very cool.

Recently, I presented on EXCEPT. I love this concept, because it is very “natural language-y”. It’s simple and effective, and I use it all the time. I had a few comments, mostly around performance and other patterns that can be used to achieve the same results. As a result of these comments, I decided that it was time for the inaugural Datachix Cage Match. So, without further ado, I present to you….

CAGE MATCH I: ANTI-JOINS!

Anti-Joins are used when we want a result set that represents this idea: Give me everything from one result set that doesn’t exist in another result set.

The Players:

NOT IN (The Old Man) – This is probably the first form of anti-join we all learn. It’s easy to understand, and it works. It’s developed a reputation as a resource-eater, and common wisdom is to avoid it.

SELECT Title, LastName 
FROM dbo.ImportBookONE 
WHERE Title +'--'+ LastName NOT IN  
  (SELECT Title +'--'+ LastName FROM dbo.ImportBookTWO );

LEFT JOIN (The Ninja) – This is the pattern that looks least like an Anti-Join. It’s an outer join coupled with a WHERE clause eliminating any rows that didn’t show results in the right query. It can be confusing if seen in the wild, because it isn’t immediately apparent that it’s an anti-join. It’s a favorite of savvy database developers.

SELECT o.Title, o.LastName 
FROM dbo.ImportBookONE o 
  LEFT JOIN dbo.ImportBookTWO t   
    ON o.Title = t.Title 
      AND o.LastName = t.LastName 
WHERE t.Title IS NULL   
  AND t.LastName IS NULL;

NOT EXISTS (The Existentialist) – NOT EXISTS has been around for a long time, and seems to get overlooked pretty often as an Anti-Join option. It comes right out and asks the age-old question, “Does what I’m looking for even exist”?

SELECT Title, LastName 
FROM dbo.ImportBookONE o 
WHERE NOT EXISTS   
  (SELECT t.Title, t.LastName
  FROM dbo.ImportBookTWO t
  WHERE t.Title = o.Title     
    AND t.LastName = o.LastName );

EXCEPT (The New Kid) – EXCEPT has been available since SQL Server 2005, and is a cousin to UNION. It’s the one I presented on, and is a personal favorite. (But I promise we’ll stick to facts for this cage match)

SELECT Title, LastName 
FROM dbo.ImportBookONE   
  EXCEPT 
SELECT Title, LastName 
FROM dbo.ImportBookTWO;

The Setup:

The cage match will consist of 4 rounds:

Round 1: iHeartBooks Match – I take the EXCEPT I wrote for the presentation I did and rewrite the Anti-Join using all 4 options

Round 2: Mini Match – 4 contestants, 10 and 8 row tables, respectively

Round 3: Big Match – 4 contestants, 332,500 rows in one table, 321,416 rows in the second

Round 4: Main Event Match – 4 contestants, 6,760,676 rows in one table, and 6,250,625 rows in the second, with a 2-part business key thrown in for good measure

The Measurements:

CPU Time – The amount of CPU time per query (in milliseconds)

Reads – The number of logical disk reads per query

Duration – The total amount of processing time for the query

Percent of Batch – The percentage of the batch taken up by the query (when all 4 anti-joins are run in a single batch) – this doesn’t really tell us much besides giving us rough comparative numbers, but it’s interesting so I’m keeping it

ROUND 1: iHeartBooks Match

Using the same database I presented with, I run the variations on Anti-Join. Below are the queries competing in Round 1:

LEFT JOIN NOT EXISTS
SELECT o.Title, o.LastName
FROM dbo.ImportBookONE o
LEFT JOIN dbo.ImportBookTWO t
 ON o.Title = t.Title
  AND o.LastName = t.LastName
WHERE t.Title IS NULL
 AND t.LastName IS NULL;
SELECT Title, LastName
FROM dbo.ImportBookONE o
WHERE  NOT EXISTS
(
SELECT t.Title, t.LastName
FROM dbo.ImportBookTWO t
WHERE t.Title = o.Title
AND t.LastName = o.LastName
);
EXCEPT NOT IN
SELECT Title, LastName
FROM dbo.ImportBookONE
EXCEPT
SELECT Title, LastName
FROM dbo.ImportBookTWO;
SELECT Title, LastName
FROM dbo.ImportBookONE
WHERE Title +'--'+ LastName NOT IN
(
SELECT Title +'--'+ LastName
FROM dbo.ImportBookTWO
);

OUTCOME:

CONTESTANT CPU READS DURATION Percent of Batch
LEFT JOIN 0 51 40 25
NOT EXISTS 15 51 6 25
EXCEPT 0 51 7 25
NOT IN 0 51 0 25

Hmmm… Not very decisive. Every query had the same number of logical reads? That’s no fun! We’re working with very few rows, and we really end up with no discernable difference between each contestant. On to Round 2!

ROUND 2: Mini Match

Now, we’re going to see if this logical reads thing holds with another set of queries. This time, we’ve got 2 very small tables, one with 10 dates and one with 8 dates. The Anti-Join will return 2 rows.

LEFT JOIN NOT EXISTS
SELECT m.CageMatchDate
FROM ExceptCageMatchTiny m
LEFT JOIN ExceptCageMatchTinySub s
ON m.CageMatchDate = s.CageMatchDate
WHERE s.CageMatchDate IS NULL;
SELECT CageMatchDate
FROM ExceptCageMatchTiny m
WHERE NOT EXISTS
(
SELECT CageMatchDate
FROM ExceptCageMatchTinySub s
WHERE m.CageMatchDate = s.CageMatchDate
);
EXCEPT NOT IN
SELECT CageMatchDate
FROM ExceptCageMatchTiny
EXCEPT
SELECT CageMatchDate
FROM ExceptCageMatchTinySub;
SELECT CageMatchDate
FROM ExceptCageMatchTiny
WHERE CageMatchDate NOT IN
  (SELECT CageMatchDate
  FROM ExceptCageMatchTinySub);

OUTCOME:

CONTESTANT CPU READS DURATION Percent of Batch
LEFT JOIN 0 31 5 19
NOT EXISTS 16 31 6 18
EXCEPT 0 31 7 45
NOT IN 15 31 6 18

So what have our first two rounds of the cage match taught us? Well, if you’re working with very small data sets, you probably won’t see much difference in performance. Look at the differential between overall duration – almost nothing. This is a great example of the trap that we as database developers tend to fall into. We work with small sample databases during development, and it allows us to be lazy. Every option looks similar, and we go with what’s familiar instead of what’ll scale. Watch what happens in Rounds 3 & 4. Also, a great example of why only looking at Percent of Batch in SSMS can be misleading… 45% of the batch was EXCEPT, even though CPU time registered 0 and overall Duration was only 7? If you’re interested in seeing a complete picture of what’s happening on a server when you hit F5 in SSMS, take the time to kick off Profiler.

ROUND 3: Big Match

Ah, now the pros are entering the cage! We’re working with over 300,000 rows in each of our two tables, and there might be some blood spilled (finally!).

LEFT JOIN NOT EXISTS
SELECT m.CageMatchDate
FROM ExceptCageMatchMain m
LEFT JOIN ExceptCageMatchSecondary s
ON m.CageMatchDate = s.CageMatchDate
WHERE s.CageMatchDate IS NULL;
SELECT CageMatchDate
FROM ExceptCageMatchMain m
WHERE NOT EXISTS
(
SELECT CageMatchDate
FROM ExceptCageMatchSecondary s
WHERE m.CageMatchDate = s.CageMatchDate
);
EXCEPT NOT IN
SELECT CageMatchDate
FROM ExceptCageMatchMain
EXCEPT
SELECT CageMatchDate
FROM ExceptCageMatchSecondary;
SELECT CageMatchDate
FROM ExceptCageMatchMain
WHERE CageMatchDate NOT IN
 (SELECT CageMatchDate
 FROM ExceptCageMatchSecondary)

OUTCOME:

CONTESTANT CPU READS DURATION Percent of Batch
LEFT JOIN 389 1912 436 7
NOT EXISTS 390 1912 372 7
EXCEPT 624 1912 705 12
NOT IN 1262 1001148 880 74

Ah, the Old Man’s starting to show some wear. Logical reads for NOT IN went through the roof, and if you look at the Execution Plan, you can see why:

Poor guy had to spool out to a temporary table to hold the rows. None of the other contestants ran into this, and it absolutely killed NOT IN. I think he might be on his last leg. Very interesting that LEFT JOIN, NOT EXISTS, and EXCEPT are still showing the same number of logical reads. I was surprised by this.

ROUND 4: The Main Event

Finally, the round you’ve all been waiting for! A chance to see a no holds barred showdown between each of the contestants. So far, NOT IN has fallen behind, but LEFT JOIN, NOT EXISTS, and EXCEPT have both held up well. In this round, we’re working with two tables containing well over 6 million rows each. Just to make it more challenging, we’ve set up a two-part key to evaluate. Let’s see the final queries and results:

LEFT JOIN NOT EXISTS
SELECT m.CageMatchAlpha,  m.CageMatchNumber
FROM ExceptCageMatchGinormous m
LEFT JOIN ExceptCageMatchGinormousSub s
ON m.CageMatchAlpha = s.CageMatchAlpha
AND m.CageMatchNumber = s.CageMatchNumber
WHERE s.CageMatchAlpha IS NULL
AND s.CageMatchNumber IS NULL;
SELECT CageMatchAlpha, CageMatchNumber
FROM ExceptCageMatchGinormous m
WHERE NOT EXISTS
(
SELECT CageMatchAlpha, CageMatchNumber
FROM ExceptCageMatchGinormousSub s
WHERE m.CageMatchAlpha = s.CageMatchAlpha
AND m.CageMatchNumber = s.CageMatchNumber
);
EXCEPT NOT IN
SELECT CageMatchAlpha, CageMatchNumber
FROM ExceptCageMatchGinormous
EXCEPT
SELECT CageMatchAlpha, CageMatchNumber
FROM ExceptCageMatchGinormousSub;
SELECT CageMatchAlpha, CageMatchNumber
FROM ExceptCageMatchGinormous
WHERE CageMatchAlpha+'-'
  +CAST(CageMatchNumber as varchar(10))
NOT IN
 (SELECT CageMatchAlpha+'-'
  +CAST(CageMatchNumber as varchar(10))
 FROM ExceptCageMatchGinormousSub);

OUTCOME:

CONTESTANT CPU READS DURATION Percent of Batch SSMS Run Time
LEFT JOIN 12446 30844 13029 8 0:14
NOT EXISTS 11483 30840 13917 7 0:13
EXCEPT 19456 30848 19988 10 0:20
NOT IN 60216 20363994 116674 76 1:52

Aha! Finally, we see some real difference in performance. The Old Man (NOT IN) has been knocked out cold. But, surprisingly, the other 3 contestants held up surprisingly well. EXCEPT started to show some cracks in the millions-of-rows arena and lagged behind LEFT JOIN and NOT EXISTS. I think I’m going to have to call it a tie between The Ninja and The Existentialist, with The New Kid in a close third.

This post was fun to research and fun to write. But there’s a moral to the story besides “NOT IN = Bad and LEFT JOIN = Good”. If you’re in a development environment, think about the size of your development data set versus what you expect your production data to look like. (Yeah, you heard me… Capacity Planning) Develop using best practices, and avoid lazy pitfalls (Like NOT IN). And most importantly, take the time to try different options. I mean, who hasn’t dreamed of refereeing a cage match?

Query on, my friends….

–Audrey

Five Places to Document Before You Win the Lottery

When a project starts to go sideways and you’re cursing Naive Past You who signed off on the “aggressive” time estimates as you feed quarters into the vending machine in hopes that powdered donuts and honey buns won’t give you a heart attack before you hit the deadline, the last thing you want to talk about is documentation. Non-developers, if you value your life, observe the developer and her surroundings before you ask whether the documentation is updated. If you see any of the following…

  • Pyramid of Diet Coke or Mountain Dew cans
  • Pile of takeout containers (half-eaten because there’s no time)
  • Wild, bloodshot eyes
  • Caffeine-induced shakes
  • Sleeping bag and/or pillow

 …Back away slowly. DO NOT, under any circumstances, ask the developer if the documentation is up to date. You may lose a valuable body part before you can wedge yourself behind the file cabinet.

I love the beginning of a project. It’s full of resolutions and promises about how this time it’s going to be different. A quick run-down of statements I’ve heard (and said):

  1. “We’re building time into the schedule to keep the documentation up to date”
  2. “All of my time estimates will include some buffer for documentation”
  3. “QA will not sign off without documentation”
  4. “We’ve made accurate documentation part of the critical path”
  5. “We’ll document as we go”

What ends up happening? I can sum it up in 4 words: “We’ll fix it later”. But we never do. Then we leave and the next poor soul comes in blind and realizes that the documentation is either non-existent or woefully out of date. He gets to spend the next few weeks being indignant and self-righteous because the documentation is bad. He quickly forgets the undocumented tangle of code he left behind at his last job.

So, what exactly is documentation? Is it a necessary evil, a sacrifice to the development lifecycle gods? Is it yet another bit of unproductive administration work you have to do to check all the boxes on the project plan? Is it some antiquated legacy of waterfall methods? No. It is none of these things. Documentation, when done well, is a conversation between you and the next guy who comes along and looks at your work. You might not be there to explain why you had to do that weird thing in that stored procedure. You might not be able to tell the story behind the funky flat table you wish your name wasn’t associated with. You might not be able to sit down over lunch and talk about the wacky business rules you’re trying to enforce. But, your documentation will be there. More than anything else, this is your legacy.

Am I proposing that you create exhaustive documentation explaining every decision and line of code? No. That’s totally unrealistic, and I believe that it’s what gets us into trouble. Overwhelmed by the goal of perfect documentation, we end up not writing any documentation at all. I fall into the good enough camp in my approach. Even then, it can be a struggle to keep up. In that spirit, I have a few must-haves and guidelines for what and when to document. Also, I’m sort of a function over form kind of girl. If it works, do it. If it’s pretty, well, that’s nice too. Of course, I’m a developer. If you’re a DBA, your list will probably look different. But, I recommend the same basic approach. What are my must-haves and what are my triggers for a bit of explanatory prose? Or, you know, poetry… but if you have time to write documentation in iambic pentameter, you probably need a more challenging job.

Must-Haves

Data Dictionary – If nothing else, get the basics down:

  1. Entities and their purpose
  2. Attributes and their definition
  3. Business names for entities and attributes
  4. Explanation or examples of what data can end up in a column

Source to Target Mapping – if you’re moving data around, make a map

  1. Where does the data come from?
  2. Where is it going?
  3. What are the stops along the way?

Anything Manual – if you have to do anything out of process or manually, write it down

  1. Bob from Accounting asked you to run a “quick little report” for him in 2009. Somehow, you’re still running it every Monday
  2. You manually kick off that pesky job every Thursday evening instead of scheduling it
  3. You had to (gasp!) manually update data in the database

Other Guidelines

In-Line Documentation – (Stored Procedures/Functions/Scripts/etc.)

  1. Set up a nice little template inside any procedure/function/script that tells: Author/Date/Purpose/Major Revisions
  2. If you did something unconventional inside some encapsulated code, make a note of why you went this direction
  3. If some piece of code started out pretty and then grew horns and a tail and bought a pitchfork from Evil Processes R Us, explain what happened
  4. If you’re working around some limitation of the application/database/etc., say how and why

In Case I Win the Lottery Documentation (aka, In Case I Get Hit by a Bus)

    Imagine that you walk out the door this afternoon, and for some wonderful (not getting hit by a bus) reason, you never step foot inside your office again. You never log into the network again, and no one knows how to get in touch with you. What do only you know? Trust me, if you’ve been with an organization for any length of time, I can guarantee that you have some proprietary knowledge. This can be the least formal of all. Just make a list of things you hope someone else knows how to do when you’re sitting in Tahiti sipping fruity drinks, ogling the very cute cabana boy as you ask him to bring you yet another extra towel.In our little SQL Server community, we talk a lot about being part of the community. (I know… totally Meta) Documentation is a great way to be a good citizen inside our world. Chances are someone you know, or someone who knows someone you know, will be coming into an organization after you leave. Helping the next guy ramp up more quickly and avoid your early stumbles is just about the best you can do to support your fellow database professional.

What Are We Doing Here, Exactly?

There’s a secret about being a developer that I forget all the time.  I know it, and I should remember it, but in the daily drama of life, I tend to forget it.  Here it is:  Knowing how to do something is the easy part.  Knowing what to do… that’s hard. 

Technical skills allow me to execute on a plan.  The good news is that if I don’t know how to do something, there is a wealth of resources out there to help me out.  I can probably pilfer a bit of code from a blog, find a checklist, or even call a friend.  Knowing the plan?  That’s the hard part. 

Brent Ozar (Blog|Twitter) wrote a brilliant post about being a consultant.  It was so brilliant and apropos that I e-mailed him to ask for advice on a few things I’m dealing with.  He was awesome, and thoughtful, and gave me some great ideas.  He even recommended a book, The Secrets of Consulting by Gerald Weinberg.  I read it, and I immediately felt better.  Everyone should read it.  It validated something that had been creeping around in the recesses of my brain:  I didn’t have a good plan.

Why not?  Well, I’d been so busy executing that I’d forgotten to take a step back and think.  It happens to the best of us.  I tend to be an intuitive person.  I feel like something’s not right long before I can put my finger on it.  It makes me crazy.  It’s like the intelligent part of my brain is whispering, “Audrey… Audrey… Pay attention.  This isn’t working”, while the rest of my brain is totally focused on crossing things off the to-do list.  The problem?  Maybe the to-do list is dead wrong. 

So that’s what development managers and project managers are for, right?  They put the plan together. They figure out the what, we figure out the how.  Right.  Right?  Hogwash!  Yeah, I said it.  Hogwash. 

Here’s what I believe.  Every person involved in a project, from the college intern to the CTO needs to do a personal assessment of the project they’re on.  I’d flat forgotten this personal belief of mine.  In the rush to deliver, I’d jumped headfirst into a project without first grounding myself.  So, after talking with Brent and reading Weinberg’s book, I assigned myself a task:  Assess the Project. 

Now, I’m not the first person to do this, and I’m certainly not the first person to talk about this.  But I constantly have to remind myself to actually do it.  It is a liberating exercise.  Putting onto paper what’s worrying me about a project makes it real.  If it’s real I can do something about it, or at least see it coming before it smacks me in the face. 

I have a few personal rules for my assessments: 
1)      It is a personal document.  For my benefit.  I might use it as a reference for later communication, but for now, it’s just me and my stream of consciousness.  I don’t worry about sounding negative or hurting feelings.  If I think it’s going to be really bad, I might even write it at home and on the personal computer.

2)      It is mostly a problem-defining exercise, not a problem-solving exercise. 

3)      No edits till I’m done.  None.  No tweaking, rewording, or rethinking.  This one is hardest for me.  I can’t help myself sometimes, and the urge to soften a harsh word or begin in-line rationalizing is tough to resist. 

4)      This is not a technical document.  It is an emotional document.  Everything from “I don’t know how to do X” to “Mr. End User refuses to cooperate” is fair game. 

Anyway, here’s my process.  I ask myself some questions, and answer them.  Really, it’s just a set of lists. 

1)      What is the current state?  – What’s going on in the business that prompted this project in the first place?  What needs to be improved/created/maintained?  Is the system too slow?  Are users complaining?  Are we losing customers? 

2)      What is the desired state?  – What does everyone want the world to look like when this project is done?  Is capacity higher?  Turnaround faster?  Errors reduced?  Is there a totally new process?  Is there a shiny new system? 

3)      What are the problems? – (Remember, we can use the politically incorrect “problem” because it’s a personal document) What’s keeping us from getting to the desired state?  What issues do we keep tripping over?  Who’s being difficult or unrealistic?  Is the schedule reasonable?  What am I awake at 4:00 AM worrying about? 

4)      What can I fix? – Here, I sort of break one of my rules.  I try to identify what I can fix that’s broken.  Key point:  What, not how.

a.       Right Now – What can I do right now without anything else happening first?  I don’t worry about time or resources; I just list everything I could theoretically fix.
b.       Right After – If I fix the things I could fix right now, what’s next?
c.       And Then?  – If I can theoretically get through the “Right Now” and “Right After”, what could I do? 
 
TANGENT 1:  It’s interesting to see if putting together these three lists naturally gets me to the desired state I defined in List 2.  BUT… I resist the urge to force it.  Be honest.
 
5)      What is my conclusion? – This is the part where I get to rant.  I just start writing about where I think this project is headed.  Hopefully, the first 4 lists I’ve put together have helped me get my head on straight.  If not, well, that tells me something too.  Seriously, I rant.  I tell it like I think it is.  No one is going to read what I say except for me.  Do I believe the project is going to fail?  I say it.  Then say why.  Do I think we need to go in a different direction?  I put it down.  Do I think I’m failing to deliver?  Why?  It’s the most liberating part of this process.  Feels like confession.

6)      What questions do I have? – I read back over my first 5 lists, and start writing down any questions I can’t answer.  Doesn’t matter how big or stupid or rude.  In the past, I’ve written thing like, “Does anyone care if this project succeeds?”, and “Can we hit deadline if [Name Redacted] keeps screwing up?”  I might never ask these questions out loud, but it’s therapeutic to ask myself.  I might even come up with a few that need real answers that I can ask in public and look proactive and smart. 

TANGENT 2:  If I can’t put the 6 lists together off the top of my head, this is a giant, flaming red flag.  If I can’t define where we are, or where we want to go, or what I can do to help get us there, I’ve got real problems. 

So, I’ve poured my thoughts and worries and soul into answering 6 basic questions.  What now?  I put it away.  I leave it alone to marinate for a day.  Then, I open it back up again.  I read it and try to see what the basic feel is.  Is it optimism or despair?  Was I overly negative, or did I apply false optimism to my lists?  And, most important, do I see the beginnings of a plan? 

Ninety-nine times out of a hundred, I see things I could be doing differently.  I can begin to filter out the things I can improve versus the things I have no control over.  I usually see a plan emerge.  I see a way out of whatever hole I’ve dug for myself (or been thrown into).  Most importantly, I have a lodestone in this assessment next time the manager asks me what I think.  I’ve already thought about it and put it on paper, and I’m not fumbling around trying to describe some general feeling of “Not Rightness”.

I don’t believe that there are impossible projects, but I do believe that there are impossible plans.  More than there should be, actually.  I know that Development Managers get sick of hearing us whine about the plan.  But, if I can say, “I have a few specific questions and ideas about the plan”, they usually sit up and listen.  See, there’s another dirty little secret that my dear friend Josh Lane told me once:  We all think there are these experts out there that have all the answers.  Guess what?  There isn’t.  It’s us.  We’re it.

Scary as hell?  Yes.  But it also means that it’s on us as developers to not just solve problems, but to help define them as well.  Ask anyone in our business… it really is harder than it looks.

Noir SQL… Or, a Hardboiled Approach to Getting the Job Done

You can tell a lot about my state of mind by the books I’m reading. Lately, it’s Urban Fantasy with a Noir feel to it. Specifically, I’m reading Mike Carey’s Felix Castor series, and I just finished a book by Richard Kadrey called Sandman Slim: A Novel. I love the anti-hero. The protagonist who is gritty and dirty and has a few great scars is my kind of guy. He unapologetically breaks the rules and isn’t all, “it’s more about the journey than the destination.” For him, destination is what matters, no matter now you got there.

Lately, I feel a bit like the scarred anti-hero. I’m doing some things in a production environment that I’m not totally thrilled about, and I wish I could stop the line and do things the “right” way. I want to use SSIS to transform data. I want to encapsulate processes into neat, repeatable, parameterized modules. But, you know what? When there’s a same-day turnaround on a request, you make do. You go a little Noir on your T-SQL, know what I mean?

I want to show you two things that I’ve actually done in the past few weeks. No, given a nice, neat environment, this SQL might never have been written. Am I proud of it? Well, yes. Yes I am. At the end of the day, I got the customer what he needed. Was it pretty? No. I’m cool with that. Being the anti-hero is kind of fun every once in a while.

Fixed-Width Output

I needed to give a guy a text file in fixed-width format. I had a process from my predecessor that just wasn’t working. The file was already late. So here’s what I did. I’m using the AdventureWorks database to show an example.

SELECT
	LEFT((ISNULL(Title,'')+SPACE(50)), 8)+
	LEFT((ISNULL(FirstName,'')+SPACE(100)), 20)+
	LEFT((ISNULL(LastName,'')+SPACE(100)), 30)+
	LEFT((ISNULL(MiddleName,'')+SPACE(100)), 5)+
	LEFT((ISNULL(EmailAddress,'')+SPACE(100)), 35)+
	LEFT((ISNULL(Phone,'')+SPACE(100)), 25)
FROM AdventureWorks.Person.Contact	;

The result:

Paste it into Notepad and see how it looks:

I save the text file and send it on. Pour myself a whiskey, neat, and light up an unfiltered Lucky Strike.  Okay, not really, but you know what I mean. 

A quick run-down:

ISNULL: If any of the values I’m concatenating are NULL, then the entire string will come back as NULL. I wrap all of my columns in ISNULL like so:

ISNULL(Title, ‘’)

This sets the value to an empty string if the value is NULL.

SPACE: This handy little string function will pad the given number of spaces onto the result you return. I want to make sure I end up with enough padded spaces to fill out the fixed-width portion of that column. So, I pad the output:

ISNULL(Title, ‘’)+SPACE(50)

This will give me the output from the Title column, plus 50 spaces.

LEFT: Now, not every value coming out of the database is going to have the exact same number of columns. So, I use the LEFT function to trim it down to the exact length I want. LEFT will take the left-most number of characters you tell it to. If I say,

LEFT((ISNULL(Title,”)+SPACE(50)), 8 )

I’m telling it to give me characters 1-8 that are returned. Since I’ve padded my output with spaces, it’ll be the result from the column, plus as many spaces as I need to pad the output to 8.

Pretty? No. Functional? Yes. Noir SQL? Absolutely.

Remove Unwanted Characters

Next up, I have a source file I use from another department. It comes in Excel format, and includes a phone number. I’m supposed to get something that looks like this: 1112223333. Nice, neat, simple. What do I get? A hodge-podge of phone number formats. I’m looking at something like this:

CREATE TABLE PhoneNumber
(
	PhoneNumber varchar(50)
); 

INSERT INTO PhoneNumber(PhoneNumber)
VALUES
	('1112223333'), ('(111) 222-3333'), ('111-222-3333'), ('111 222 3333'); 	

SELECT PhoneNumber
FROM PhoneNumber

Okay. So I need to clean these numbers up quickly. Destination, not journey, my friends. I’m the anti-hero. I import the data into SQL Server using the Import/Export utility so I can manipulate the data. Then, I run this nifty little REPLACE statement:

SELECT PhoneNumber,
	CASE
	WHEN ISNUMERIC(PhoneNumber) = 0
		THEN REPLACE(
			REPLACE(
				REPLACE(
					REPLACE(PhoneNumber, '-', ''),			--Strip out dashes
				' ', ''),							--Strip out spaces
			')', ''),								--Strip out close parenthesis
		'(', '')									--Strip out open parenthesis
		ELSE PhoneNumber
	END as FormattedPhoneNumber
FROM dbo.PhoneNumber

Check out the results:

Sweet. It’s quick, it’s dirty, and it saved me having to wait on the source data provider to clean things up on his end. I turn the query into an UPDATE statement, and I’ve got clean data to import.  Again, a run-down of the functions:

ISNUMERIC: Tells me whether the value I’m passing is qualifies as a number or not. NOTE: It recognizes hexadecimal as a number, so use carefully. I set up a CASE statement that asks if the value is numeric. If it is, that means I don’t have any characters like “(“, “)”, or “-“ in there. If not, I apply a nested REPLACE to the value.

REPLACE: Replace is awesome. I can say something like this: REPLACE(PhoneNumber, ‘-‘, ‘’). This is saying that if I find a dash, I want to replace it with an empty string. What’s really cool is that I can nest them. So, I can tell it to remove the dashes, then the spaces, then the open parenthesis, and finally the close parenthesis in one statement.

Bottom line: Sometimes things just have to get done. The difference between an anti-hero and a true antagonist is that we anti-heroes know to go back and do things the right way as soon as we get a moment to breathe. In the meantime, don’t apologize for leaving behind a few unmarked graves when you need to get the job done. We’re anti-heroes. We have the scars to prove it.

T-SQL Tuesday #14 – Audrey’s (Career) Aspirations for 2011

Here we are again for another T-SQL Tuesday. This month’s event is being hosted by MidnightDBA (Blog|Twitter). The whole crazy concept is the brainchild of Adam Machanic (Blog|Twitter). If you’re interested in what this thing is about, check out this month’s invite.  A quick bit of gushing praise for the whole T-SQL Tuesday thing: For me, deciding what to write about is the hardest part of blogging. When someone tells me, “Hey Audrey. Write about this. And we promise a ton of people will come read about it”, well, you don’t have to tell me twice. I’m on it like the paparazzi at a celebutante convention.

The topic of this month’s event is “Resolutions”, which is, you know, totally apropos since it’s the first month of the year and all. Personally, I don’t make many resolutions, and they’re usually boring. For example, last year’s personal resolutions included: 1) Lose 10 pounds (I didn’t) 2) Drink more water (I did), and 3) Clear out clutter (Sort of, a little). I do establish career goals, and as my mama always said, “They aren’t real until you write them down”. She was a list-maker. She would make a list, and if she did something that wasn’t on the list, she’d add it just so she could cross it off. Watching her made me a list-maker too. It’s part of my morning routine to sit down and write down what I’m trying to accomplish that day. Yes, I said write. Like with pen and paper. I need the ritual. So it makes sense to get my to-do list together for 2011. Thanks to MidnightDBA for giving me a good excuse to really think about it.

Audrey’s (Career) Aspirations for 2011

1) Learn more about SQL Server internals. I’ll be reading Microsoft SQL Server 2008 Internals by Kalen Delaney (Blog|Twitter), Paul S. Randal (Blog|Twitter), Kimberly L. Tripp (Blog|Twitter), Conor Cunningham (Blog), and Adam Machanic (info in 1st paragraph). This book has been sitting on my shelf, glaring at me for not reading the entire thing for far too long. It’s time to go cover-to-cover, baby.

2) Learn more about Analytics, using SQL Server as well as other products. I want to get better at the UDM/cube/presentation portion of the BI Stack. I’m still figuring out the right approach for this aspiration. But, I can assure you that Project Crescent and BISM are somewhere on the plan.

TANGENT: When I hear Project Crescent, I immediately think “crescent roll”, and then I think “Crescent City” which is New Orleans, and then I think of beignets, because they are tiny bits of powdered sugar-dusted heaven. Then, I get a little homesick, because there aren’t many places around Atlanta where you can get a good beignet. I grew up near Houston, which is close enough to NOLA that the good food tended to bleed over into our part of Texas. They should have just named it Project Beignet to save me the time it takes to get through my stream of consciousness. :END TANGENT

3) Re-read The Data Warehouse Toolkit by Ralph Kimball and Margy Ross. Why? Well, I haven’t read it in a couple of years, and I have a build-out of a dimensional model on my plate. Reading this book again is like stretching before the big game. I’ll feel warmed-up and ready to go when the project really gets rolling.

4) Learn PowerShell (for SQL Server). You know, there are a lot of reasons for learning PowerShell, but the one that motivates me the most is this: My first programming language was Turbo Pascal 7.0 during my senior year of high school in 1994. (I’ll save you the math… I just turned 35. [sigh…]) My first RDBMS was Oracle 7.3 in 1996. Neither had snazzy GUI’s to help me limp along as I was learning. I love how my world has come full-circle. Here we are in 2011, and people are singing the praises of a non-GUI-based way to interact with SQL Server. I want in on the fun. AND… I’m tired of hearing from Aaron Nelson (Blog|Twitter) about how great it is and not knowing for myself.

5) Finish my certifications. I got my MCTS certs for SQL Server 2008 Business Intelligence and SQL Server 2008 Database Development late last year. I promised myself that I’d go on to the MCITP exams this year. This one has a timeline too… I’ll get both before the end of the summer. I’m toying with the idea of going after SQL Server 2008 Database Administration too. You know, because they’re there. And I’m so ridiculously competitive that the idea of leaving the tests untaken is bothering me.

6) Blog, and blog consistently. My fellow Datachix, Julie Smith (Blog|Twitter) and I promised each other that we’d each blog every other week. That’s 26 blog posts for me this year. I’ll go a step further and say that 20 will be technical posts. You’re not getting much fluff from me this year, my friends.

7) Speak. I will present more. I will learn to present virtually. I will rock the house with my awesome, well-prepared, techically relevant, and entertaining presentations.

8 ) Finally, I will be a great consultant. I already know how I’m spending 2011 from a client standpoint, and I’ll be working to make sure that they look back on 2011 and remember it as the year that they finally got their data straight, their processes together, and their analytics moving in the right direction.

So, in conclusion, my 2011 aspirations are: Read, Study, Read, Learn, Test, Blog, Speak, Rock. Easy-peasy, right? Whoo-boy! I’ve got to go. I need to get started.

Wait, before I go… My 2011 aspiration for all of you is that you have a wonderful, satisfying, and all-around kick-ass year. Make time for the things you love. Learn something new. Try something you’re scared of. Make some new friends. Go for a walk in the rain without an umbrella. Watch a sunset or two… and maybe a sunrise. Tell the people you care about how awesome they are. When presented with a great opportunity, go for it. When 2011-12-31 23:59:59.999 rolls around, you’ll be glad you did. I’m totally rooting for you!

Thanks for reading all the way through my list, and if you see me, ask me how I’m doing on it. There’s nothing quite like public accountability to keep me honest.

Rock on, my friends…

—Audrey