Jaa

What I Know Now: Ward’s Epistle to the N00bs

  • Artikkeli

And I remember what she said to me
How she swore that it never would end
I remember how she held me, oh so tight
Wish I didn't know now what I didn't know then

Bob Seger, Against the Wind

All you need is love
Love is all you need

John Lennon and Paul McCartney, All You Need is Love

My pal Jimmy May apparently feels that my to-do list is insufficiently populated, but I’ve got to say he picked a good topic. He’s tagged me in a discussion regarding what advice we’d give SQL n00bs (which also includes Kevin Kline, Mike Walsh, Michelle Ufford (the SQL Fool), Chris Shaw, John "El Magnifico" Magnabosco, and Tim Mitchell). As I said, cool topic..

So, I’ll give two pieces of advice: one that’s nuts-and-bolts technical, and one that’s warm and fuzzy and Zen (and I’ll also note that I’ve attempted to dispense this sort career advice a few time before via the Pond’s Laws series).

The Zen comes first: I’m asked from time to time by people to identify the key to my modest success, and I always say, “respect for my ignorance.” While I’m trying to be inscrutable when I say that, here’s what I mean:

There are guidelines aplenty, but there should be no religious arguments in the database design and programming business

Performance should be king

Or.. to use my favored, more inscrutable phrasing..

The key to success in this business is to learn to speak lovingly to the optimizer

It’s not about “writing a left outer join” or “building the results in one query instead of three” or “never using a cursor.” None of these constructs is inherently good or evil, although all of them can provide magnificent results in one scenario and scatological results in another.

SQL Server, and its optimizer in particular, are pieces of software code. As such, they’re coded to work best when “addressed” (via schema and code) in a certain fashion.

The key is to find the most efficient way possible – from hardware to disk layouts to schema to T-SQL/foundational code to application code to interface code – to render data accurately, and get it from its creators and to its consumers as quickly and efficiently as possible.

With the benefit of hindsight, I know now that early in my career, I worked with several senior people who only knew one way to do things (names withheld and not available on request). This led to an environment where creativity and experimentation were discouraged and, to some extent, career-limiting.

One incidental fallout of this sort of organizational culture is that people become afraid to admit when they don’t know something. After all, the zeitgeist goes, there’s only one way to do things – how can you not know it? I’m convinced that this syndrome is the genesis of a great deal of the inefficiency in the corporate world.

When this sort of person is asked to do something new and different, where “same old, same old” won’t get the job done, rather than breaking out the nine most powerful words in the English language – let me find out and get back to you – they break out a shovel or a pair of tap shoes. 

I cited one example of the results of this shoddy approach way back in aught-five. What if doctors worked that way? Yikes!

So.. part of my “epistle to the n00bs” is.. approach your work with an open mind. Never be afraid to challenge what you think you know.

Now.. for the nuts-and-bolts technical advice.. every T-SQL n00b I’ve ever met has gone through a period of fascination with triggers. Wow, they think, I can get work done for free every time I insert/update/delete a record. They end up inserting all sorts of (generally interesting but set-unfriendly) code into their triggers (my favorite being, “let’s send an email every time we delete a record”), and then found themselves nonplussed when their insert/update/delete performance was poor.

In a nutshell..

Triggers are not as cool as you think they are

In my experience, even the judicious use of triggers is only required to work around a late-breaking design error. They are singularly marvelous for this application (I won kudos a couple of years ago for avoiding a three-month rewrite by recommending a trigger-based solution which was designed and coded in two days). However, I am always skeptical of a design which involves anything beyond the most rudimentary “audit table/audit column” triggers (which, it should be noted, are rendered largely obsolete by Change Data Capture in SQL Server 2008), and I’m frequently able to build more performant solutions with other constructs (defaults, views, user-defined functions, stored procedures, etc.).

Thanks to Jimmy for including me in this meme. It was a fun way to spend an hour on a sleepy Sunday.

Since we all seem to be tagging three people, I’ll follow Anders Osborne’s advice and Aim Way High..

-wp

this copyrighted material was originally posted at https://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Comments

  • Anonymous
    January 01, 2003
    @Kevin:  Ha! Where exactly did you look to determine the gender of the optimizer? g

  • Anonymous
    January 01, 2003
    I’m a little late to the party here, but this is pretty apropos of some of our discussions here lately.

  • Anonymous
    January 01, 2003
    I don't usually respond when I'm tagged with these blog memes, but this one is especially interesting

  • Anonymous
    January 01, 2003
    @Adam: I liked it just fine the way you said it the first time..  g

  • Anonymous
    January 01, 2003
    @Peso:  Agreed.  My point was that many people go through a period of fascination with triggers when they overuse them. Beyond the audit table/audit column scenario, where would you use triggers?

  • Anonymous
    March 15, 2009
    The comment has been removed

  • Anonymous
    March 15, 2009
    The optimizer is DEFINITELY a woman, and I'll just leave it at that.

  • Anonymous
    March 15, 2009
    That should have said "female". <g>

  • Anonymous
    March 16, 2009
    Change Data Capture is a good thing, if you can afford Enterprise Edition of Microsoft SQL Server. For those using Standard Edition, triggers still do have their place.

  • Anonymous
    March 16, 2009
    <The optimizer is DEFINITELY a woman, and I'll just leave it at that.> OK - I am just not sure that I am sure how to take this... Are you implying that the optimizer has the great feminine advantages to multi-task?  

  • Anonymous
    March 18, 2009
    My favorite here is "Never be afraid to challenge what you think you know" which resonates with Powell's "Don't be afraid to challenge the pros, even in their own backyard." I like both.