TIL #2 – T-SQL Refactoring w. Mike Donnelly

Leave a comment

Today for my TIL Holiday Learning Series selection, I decided to watch Mike Donnelly’s (b|t) Summit 2014 Session – Twice in a Lifetime: T-SQL Refactoring 101. He’s become a good friend over the years, but I’ve yet to see one of his sessions at a SQL Saturday/User Group.  So I thought watching his Summit Session would be fun. As a hardcore T-SQL developer myself, I was really curious to see what insights he had to offer.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • Mike opens by telling a personal story, which frames the remaider of the session. A story that many of us can relate to.  He then shares the process of what he had to go through.  Great technique as a presenter!
  • While discussing T-SQL anti-patterns, Mike brings up Table Variables. As an addendum, he mentions Trace Flag 2453, which aids in addressing what I argue is the biggest weakess of Table Variables – nonsense cardinality estimation of 1. There are trade-offs to using this Trace Flag, which has been blogged about elsewhere.
  • Mike presents a list of common anti-patterns.  Idea came to me, to write up a Quick Reference/Cheat Sheet, the next time I am faced with auditing a large codebase for refactoring.  While many of the anti-patterns are now ones I recognize immediately, others are more uncommon/obscure, that I don’t always remember immediately.  A cheat sheet or checklist would be a useful personal tool, or one to share with a team.  And one which I’ll add to my personal backlog of things to do (and probably blog about).
  • Mike discusses Deprecated Features and one I wasn’t aware of was Three (or more) part COLUMN naming.
    Example:

    SELECT
    schemaName.tableName.columnOne,
    schemaName.tableName.columnTwo,
    schemaName.tableName.columnThree
    FROM databaseName.schemaName.tableName

    In the above example, the column references will be limited to two parts only: object name & column name. But in non-column references, multi-part naming is still allowed.  I find this one interesting in particular, because a former colleague was using a refactoring tool, which was reformatting his code into the 3 part column name format!  Whoops!

I really enjoyed Mike’s presentation.  Instead of taking the angle of refactoring existing code, it could easily also be presented in the context of practices when developing new code.  It is definitely a solid session that I’d recommend to anyone who finds themselves writing, auditing, and/or refactoring T-SQL code.

TIL #1 – Dynamic SQL w. Jeremiah Peschka

1 Comment

To kick off my TIL Holiday SQL Learning Series, I decided to watch Jeremiah Peschka’s (b|t) Summit 2014 Session – Dynamic SQL: Build Fast, Flexible Queries. I’ve been using Dynamic SQL for a terribly long time now, so wanted to refresh & validate what I already knew.

NOTE: In the interest of not resummarizing each presenter’s entire session, I’m not going into great detail or documenting everything I learned. After all, I want to encourage everyone to view the session for themselves.

Interesting notes & tidbits:

  • Knew about QUOTENAME() but never about PARSENAME(). The latter is pretty neat and can be useful for other object validation scenarios.
  • Never thought of using Dynamic SQL to add/omit JOINs, based on the existence of data. This can aid in elimination of LEFT OUTER JOINs & reduce unnecessary I/O & streamline an execution plan.
  • Use a @NewLine variable to format your Dynamic SQL. CHAR(13) & CHAR(10). After all, we format our normal code (hopefully), for readability.
  • Validation – I’ve used a @Debug variable flag for a while, which is a tip Jeremiah recommended too.
  • Troubleshooting Tip – Start using comments INSIDE of your Dynamic SQL strings, particularly listing the “source.” Also use slightly different variable names inside Dynamic SQL vs outside to aid diagnosis. Ex: @MyVariableOne vs @my_variable_one.
  • In the final demo, Jeremiah shares an awesome approach to making PIVOT much less painful. Watching the entire presentation is worth this demo alone!

I really enjoyed Jeremiah’s presentation.  Thrilled that I was able to glean some new insights, while validating techniques I’ve already been using.  If you ever use Dynamic SQL, be sure to check it out!

TIL – My SQL Server Holiday Training Challenge

Leave a comment

While I am between jobs, I have three weeks off! Whoo woo, when was the last time you had that much time off? Some of it will be consumed by Christmas & New Year’s holiday related shennanigans, but there’s still tons of time to fill. In addition to video games and house projects, I also thought about what I can do that’s SQL Server related. Conveniently, PASS released the Summit 2014 Session Recordings last night! And that gave me an idea.

Starting today, I am challenging myself watch a Summit session, then try to write a short TIL (Today I Learned) blog about it – even if it’s just a few short notes and thoughts. I know that I realistically cannot watch one EVERY single day, so I am challenging myself to reach a goal of watching 15 sessions. I have 23 calendar days to accomplish this.

And if you missed the announcement, PASS has made the Summit Session Recordings available for purchase, regardless of whether you attended Summit or not (though costs differ if you did/didn’t). Check it out here! And if you ask me, $495 is a BARGAIN for all of the content you get access to!

Time to get my SQL Learning On!

  1. TIL #1 – Dynamic SQL w. Jeremiah Peschka
  2. TIL #2 – T-SQL Refactoring w. Mike Donnelly
  3. TIL #3 – SQL Server in Azure VMs w. Buck Woody
  4. TIL #4 – Five Execution Plan Patterns to Watch For w. Erin Stellato
  5. TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland
  6. TIL #6 – Achieving Peak Performance from your Virtual SQL Servers w. David Klee

T-SQL Tuesday #61: Giving Back

1 Comment

https://sqlbek.wordpress.com/wp-content/uploads/2014/05/tsql2sday150x150.jpg

Welcome back for another edition of T-SQL Tuesday!

This month, Wayne Sheffield (b|t) is our host! His chosen topic is Giving Back. More specifically, he asked each of us to think & blog about what we plan to do in 2015.

Chicago Suburban User Group

Coincidentally, Lowry Kozlowski & I toured our new meeting location at DeVry last night. Then over dinner, we started making various plans for the first half of 2015. We brainstormed about new ideas and how to better serve our User Group audience.

SQL Saturday Chicago

Volunteering at SQL Saturday Chicago a few years ago, wound up being a pivotal decision, which lead to many other awesome opportunities within the SQL Server Community. I am happy to count myself amongst the Organizing & Planning staff, and looking forward to a fantastic event in 2015!

Speaking

I’ve had a phenomenal time speaking at SQL Saturdays and User Groups in 2014, and have no intention of slowing down. My first presentation, Every Byte Counts, has far more interest than I ever could have imagined! I’m looking forward to presenting it to others throughout 2015. In fact, I’ll be starting 2015 with Nashville’s SQL Saturday in January! I have ideas for my next two presentations that I intend to develop & debut in 2015.

That’s a brief outline of my plans to give back to the SQL Server community in 2015. Thank you to everyone in the community, who have given something to me. I encourage all of you to give back in some way. You don’t have to do anything extravagant. Consider volunteering at a SQL Saturday or User Group… or hang out on Twitter & chime in on #sqlhelp questions that you can answer! We all benefit and grow by being a part of this community!

New Opportunities

Leave a comment

It’s not quite the end of 2014, but I must say that it’s been a year full of new opportunities. In March, I began Presenting. In the summer, I rebooted the Chicago Suburban SQL Server User Group. And now, I’ll be embarking on a new opportunity.

I am thrilled to share that I’ll be Rise Health/Best Doctors as a Senior Database Developer, starting in January. I’m really excited to face new challenges & help them leverage SQL Server to its fullest. And I’m really excited to work with new colleagues, and to introduce them to the SQL Server community and our knowledge-sharing resources.

I noticed in the past few weeks that I’ve seen a number of “new job” announcements from other #SQLFamily. As excited as I am for myself, I am excited for all of us and our industry in general. It is a good time to be a data professional and I strongly believe that our SQL Server community has helped all of us take our game to the next level.

Here’s to a great 2015!

The show goes on…

Leave a comment

And on and on and on! What have I been up to? Here’s a quick list!

In August, I had the pleasure & honor of present at SQL Saturday Indianapolis and at MADPASS. This coming weekend, I’ll be presenting at SQL Saturday Minneapolis. Then the next weekend, I’ll be travelling to Seattle for PASS Summit!

Additionally, I now have two extremely successful Chicago Suburban User Group meetings under my belt! In September, we welcomed Brent Ozar for our inaugural “reboot” meeting, then had the pleasure of welcoming the SQLSkills crew in October. Both meetings were filled to capacity!

Here’s one anecdote that I want to write about here – more to remember for myself than anything else. Prior to our first meeting, we sent out a Feedback Survey, asking our membership what they wanted out of the UG. One priority was “networking.” I thought really hard about how we could facilitate this – and came up with an idea only an hour before the meeting.

During our announcements, I explained the feedback that we had received. So I asked that everyone simply turn to the person next to them, and simply introduce themselves. All I asked was that they share their name and where they work. I went on to explain that the people in attendance will become faces that we will continue to see month after month. But having names to those faces, will hopefully help people build bonds and relationships over time.

I figured this would maybe last 2 or 3 minutes, before it died down, and we’d proceed to our Q&A segment. Instead people talked… and talked… for the next half hour! It was FANTASTIC! The following month, when I did it again, Paul Randal waved me over and noted that he’d never seen a UG do this before! It really surprised me by made me feel good that my simple idea wound up working out wonderfully.

So things are really looking up right now. I’m super excited about this next SQL Saturday & PASS Summit. If you’re at Summit, do come find me and say hello!

T-SQL Tuesday #59: Pass It On

Leave a comment

TSQL2sDay150x150
This month’s T-SQL Tuesday is brought to you by our host Tracy McKibben (b|t).  Today is Ada Lovelace Day, and my good friend Wendy Pastrick (b|t) suggested we ride on that.  As Ada Lovelace had influenced her, we are all writing about Heroes who have influenced us.

For me, a key influence for me is a person I’ve never met, but read about.  In high school, I developed an interest in military history, particularly the exploits of various Special Forces units, including the Navy SEALs.  And this influence comes from retired Navy SEAL Richard Marcinko’s autobiography Rogue Warrior.

Early in his career, Marcinko served under a Navy Chief named Ev Barrett.  Here is an excerpt from Marcinko’s book, detailing a conversation they had before Marcinko left to enter Officer Candidate School:

(Chief Barrett): “Look,” he said, “you’ve learned a lot of stuff now. And you’re gonna learn a lot more.”
I nodded. “Yeah?”
“So I want you to promise me something. I want your word that what you learn, you’ll pass on.”
“Sure.” I wasn’t certain what he was getting at.
“You’re wondering what the fuck I’m saying, right?”
“Uh-huh.”
“Dick, it shouldn’t matter whether you work with a guy once or whether you serve with him for years – you gotta treat him the same. You gotta help him do his job. Like I helped you – now it’s gonna be your turn to pass it on.”
. . .
“Think of it as Barrett’s First Law of the Sea… “

It’s been about 20 years since I first read that – hard for me to believe.  But even as a teenager, it made a huge impression on me.  Even then, I recognized that many had helped me directly and indirectly, and I had a responsibility and duty to do the same for others.

And now I find myself blessed to be a part of the pheonomen we know as SQLFamily.  I treasure it because we embrace Chief Barrett’s First Law of the Sea.

We pass it on.

T-SQL Tuesday #57: A #SQLFamily Story

Leave a comment

TSQL2sDay150x150

I’m back for another T-SQL Tuesday! Today’s is hosted by Jeffrey Verheul (b|t) and the topic is #SQLFamily.

I’ve already written another post about how SQLFamily Got Me Here.  Instead of rehashing that, I thought I’d share a different story today.

The other day, Kathi Kellenberger posted this on the #SQLFamily Facebook Group Page:

Kathi Kellenberger – August 10 at 12:16pm
I had a nice conversation with Andy Yun yesterday at #SQLSat304. We met last year in Chicago during a Cards against Humanity game. This year, Andy is a speaker and getting recognition in the community. This is why we call it family. Sometimes we fight, but mostly we lift each other up.

“We lift each other up.”

Flash back to SQL Saturday Chicago 2013 & the After-Party. Kathi needed a lift back to her hotel, which I was more than happy to provide. It was extremely late, maybe midnight? During the course of the short drive back, Kathi noted that she needed to get a cab to the airport early in the morning. Being the Chicago suburbs, I pointed out that cabs don’t simply hang out at hotels. They must be pre-scheduled, especially for airport runs. I suggested she talk to the hotel front desk to see if there was a 24 hour cab company she could call to make a last-minute arrangement.

And this is where being SQLFamily comes in. I didn’t just stop with that suggestion, because I didn’t want her to risk getting stranded. As a contingency, I offered to come back in the morning and drive her to the airport. Let’s be clear to what this would have entailed. I would be driving 30 minutes back home, crashing around 1-2AM, then getting back up around 5AM, driving 30 minutes back to Kathi’s hotel, then driving her 45 minutes to the airport, before finally being able to go back home which would have been another 45 minutes. This wasn’t a trivial amount of driving, especially on very little sleep.

Yet I never hesitated to offer. In fact, I gave her my number and said that unless she was able to successfully make other arrangements, that I’d come back in a few hours to take her. This is a person I’d only met a few hours before.

Why did I do this without hesitation?

Because that’s what SQLFamily means to me. Going above and beyond, for those who are not simply just industry colleagues and acquaintences. Embracing the ideal of SQLFamily means taking it to the next level. And that’s what I was ready to do for Kathi.

SQL PASS Performance Virtual Chapter – Performance Palooza Post-Presentation

Leave a comment

Whew, how’s that for a title?

Earlier today, I had the privilege of being invited to present my Every Byte Counts presentation, for the SQL PASS Perf. Virtual Chapter. This month, they held their Performance Palooza event – 8 hours of back to back presentations! As I half joked on Twitter, I said come see “7 rockstars and a rookie (me)!”

I must say, I had a very fun time presenting today. Taking some advice from Erin Stellato, I kept focused, energized, and upbeat throughout. My friend David Klee wound up being my moderator, which was also a blessing. It really helped to calm my nerves, chatting with a friend, before we got rolling.

If you attended, thank you!! I’m told that I had 118 attendees – a number that still blows me away! I hope everyone was able to come away from my presentation with at least one useful tidbit of new knowledge.

I’ve handed over a copy of my slides & demo scripts to the Perf VC, who will have it posted on their Event Archive shortly. The session was recorded, and it should be posted in about a week’s time.

Thank you Gina Meronek, who suggested me to the Perf VC staff.  Thank you Carlos Bossy, for inviting me to present and answering all of my rookie questions!  Thank you Ryan Adams and anyone else who works on the Perf VC, for this awesome opportunity!

And thanks to all of my friends who attended, and for your tweets of support and praise!

http://performance.sqlpass.org

P.S. I’ll be presenting again in a few weeks, at SQL Saturday Indianapolis. I’ll also be visiting MADPASS in about a month, to present there as well. Finally, I’ve submitted to SQL Saturday Minneapolis, so if I’m lucky, you can see me there too!

T-SQL Tuesday #54: Are you Resourceful?

1 Comment

TSQL2sDay150x150

I’m happy to have the opportunity to participate in another T-SQL Tuesday. This month’s is hosted by Boris Hristov (b|t), who asked the community to talk about “Interviews and Hiring.”

Over the course of my career, I’ve had the opportunity to participate in many interviews, on both sides of the table. And regardless of where I’ve sat, I’ve found that my interviews have evolved to focus around one general theme:

RESOURCEFULNESS

I’ll be honest – pure technical interviews annoy the hell out of me. Anyone can memorize a book. I’ve encountered numerous candidates who have committed a slew of SQL Server trivia to memory, but do not know how to put that knowledge into action. And a purely technical interview does nothing to prove whether a data professional can succeed in a real business environment, where book answers are simply not enough to accomplish the task at hand.

The real business world demands that a successful candidate other skills outside of technical knowledge. Strong written and verbal communication skills is an example of one. And for me, resourcefulness is king.

 

What is resourcefulness?

For the sake of this dialogue, I define resourcefulness as those who may not have an answer to a challenge they are facing, but know what options are open and available to them to find that answer. Not only should they know their options, they should know when to use which options, and when to escalate properly and effectively.

H.R. Giger, the brilliant mind behind the “Alien” creature from one of my favorite film franchises, passed away earlier today. As a result, my favorite film of the series, Aliens, is on my mind and I find myself drawing some amusing parallels. Hope you’ve seen Aliens… and if you haven’t, please go watch it… I’ll wait.

Here’s one of my favorite exchanges between some Colonial Maries, as they are transported to their destination:

Ripley: How many drops is this for you, Lieutenant?

Gorman: Thirty eight… simulated.

Vasquez: How many COMBAT drops?

Gorman: Uh, two. Including this one.

Drake: Shit.

Hudson: Oh, man…

Lieutenant Gorman had a good amount of training & book knowledge. But his lack of experience fails him. And instead of exhibiting resourcefulness, he locks up, which results in his team getting decimated. He did not exercise any of the options that were available to him, to properly react to the alien threat.

As data professionals, we too must prepare for events like disaster recovery scenarios. And when disaster strikes, disasters rarely go “by the book.” We get forced to think on the fly, make use of our wits, and call upon the help of our colleagues. To me, that is being resourceful.

 

So how does Resourcefulness manifest itself during an interview?

As an interviewer, I carefully choose questions that force a candidate to demonstrate resourcefulness. Some are anecdotal, such as asking about past challenging experiences. Others are hypothetical, such as presenting them with a scenario and walking through it together. I carefully analyze not only what they answer, but how they answer.

  • Did they ask questions of other SQL Server colleagues?
  • Did they researching known SQL Server blogs?
  • Did they mention using #SQLHelp on Twitter?

All of these example options, are options that are available to all of us.

But Andy, not everyone knows about the wealth of resources that are freely available!  That’s absolutely correct! There was a time when I was ignorant of the awesome SQL Server community & failed to make use of it. So then that begs the question, how do you react when you do learn of it?

I’ve come across candidates who were in the same boat, and no, I did not judge them for negatively for their ignorance. What I did do is take the opportunity to talk about the SQL Server community and how valuable of a resource it is. And I absolutely did judge candidates on their reactions. Were they excited? Did they ask to find out more? Or were they dismissive? Did they not care?

Resourcefulness isn’t only about knowing what options you have available, but about embracing new options when they become available to you!

So ask yourself, do you think you’re a resourceful individual?  The next time you find yourself as an interviewee, think about how you can display your resourcefulness.  Or if you find yourself as an interviewer, consider how assessing a candidate’s resourcefulness will help you make a better hiring decision.