Today we’re going to try to extend some advice that I once heard from SQL MVP Jeff Moden, which I wrote about in my previous blog entitled “Make it Work, Make it Fast, Make it Pretty.”  That advice was just as pertinent then as it is now, but perhaps I can add to it what I think makes for really great T-SQL code.

Great T-SQL code is going to start with all of the advice that I gave in that blog.  That same advice really applies to any coding language, not just T-SQL.

But great T-SQL code is actually much more than that.  Let’s look at some of the aspects that transcend what I’ve previously written.

Elegance of a Set-based Algorithm

Obviously in our T-SQL we’d like to use predominately set-based algorithms.  Depending on the problem being addressed, oftentimes there are just a few very elegant solutions, and probably a bunch of solutions that are not nearly as elegant.

In simplicity there is often elegance.  Simplicity, elegance and speed may unfortunately not all play well together.  But if you can come up with a solution that is elegant, simple and fast, that’s usually going to get you a lot closer to writing great T-SQL.

Reusability

From Facts and Fallacies of Software Engineering by Robert L. Glass, here is Fact #18: “There are two “rules of three” in reuse: (a) It is three times as difficult to build reusable components as single use components, and (b) a reusable component should be tried out in three different applications before it will be sufficiently general to accept into a reuse library.”

I will suggest that in order to be great, T-SQL code must be reusable.  I’ve written before that well-focused code that solves only a specific problem is probably going to be a whole lot faster than more generalized code that solves a wider variety of problems.  So that means it should be focused on solving a common problem that occurs frequently, so that it can be reused (as a tool) whenever the need for it arises.

I tend to agree with Mr. Glass in this case; finding a common problem and then building a fast-performing, simple and elegant T-SQL solution is probably a lot more challenging than building any other one-off query.  Part of this challenge of course is simply finding a suitably simple problem to focus on, which is also encountered commonly enough to want to build something that’s reusable.

Frequency and Quality of its Reuse

Since beginning my study of T-SQL, and of course I remain a student of the subject to this day, I’ve seen a fair number of really elegant solutions to specific problems.  Of the ones that can be classified as reusable, I’ve seen many that solve well a somewhat common problem, but unfortunately the problem itself is not so common that the solution will be reused often.  Let’s face it, maybe you’ve got a solution for Gaps and Islands that is outrageously wonderful, simple, fast and elegant.  But Gaps and Islands isn’t a particularly common problem to encounter.  Sure you see it once in a while, and certainly it is good to have an elegant solution just waiting in the wings for when you need it.  But commonality of the problem is a great influencer, at least to me, of what makes for great T-SQL.

Quality of reuse is a different story entirely.  So let’s say you’ve got this (arguably) great solution and it sees quite a fair bit of reuse.  Who are the folks that are reusing it?  Are they peers in the SQL community, are they SQL Server MVPs who have embraced your method thus utilizing it themselves?  Or is it the great unwashed masses?

Facebook is a great application because millions of people use it.  But who are those users?  I’d say it is a much higher quality pattern of reuse, to have a group of peers or those that are more skilled than you are, using your reusable code, rather than just anybody else in the world.  So while Facebook may be a great app, it may not represent great code!  The quality of the people reusing your code counts for something.

Productivity

Most great T-SQL code should enhance your productivity.  By that I mean, if you’re a developer it can be considered a tool that allows you to avoid spending awkward moments of your development time solving silly sub-problems, when really you need to be focusing on solving the bigger problems that relate to the business solutions you’re trying to produce.  As a DBA, your productivity can be enhanced by any T-SQL script that automates some activity that allows you to be more proactive in your database monitoring activities.

Of course, just because you can solve a problem more quickly because the sub-problems have already been addressed doesn’t mean you should sacrifice performance.  But since great T-SQL should already be pretty fast, you may find that using the tool makes the solution to your bigger problem fast enough!  Ultimately you may have to spend some time to make it even faster, but at least you’ve gotten through the first hurdle of a development project, which is to just get it working.

Encapsulation

Any great T-SQL solution is one that can be encapsulated such that it can be used as a “black box,” without the need for users (or more importantly maintainers) of the using bit of T-SQL, to need to worry about how it works.

Yes, they should be able to look under the covers, so that when need be they can figure out how it works.  But mostly the encapsulations simply makes a great piece of T-SQL, which may otherwise be elegant or complex, really, really easy to use.

My Vote for the Greatest Piece of T-SQL

Arguably the greatest piece of T-SQL code that I’ve ever seen written is the DelimitedSplit8K string splitter.  While Jeff Moden probably wasn’t the originator of the concept of splitting a string, his first attempt at writing a fast string splitter (Tally OH! An Improved SQL 8K “CSV Splitter” Function) was pretty awesome.  I’d say that it clearly meets all of the criteria I’ve mentioned so far in this blog, and with more than 50,000 views of that article as of this writing I’d imagine there’s more than just a few people that agree with me.

Jeff really did a great job of setting up a proper test harness and proving the speed potential of the algorithm, so whether or not he claims to be the author of this wonderful little function, he’ll be the one everyone remembers once all of the dust has settled.

It has also achieved another level of greatness, which is unmatched in my experience.  That is that an entire community has sprung up around it.  There have over the years since its original publication, been numerous contributors to the evolution of DelimitedSplit8K.  These contributions have spanned more than 75 pages of discussion thread on the topic (again, as of this writing).

Alas, there are too many contributors to mention all of them by name, but suffice it to say that list includes some really great SQL talent.  Some have focused on small tweaks, others have focused on producing better test harnesses.  Others have written custom CLRs to improve the speed even further, when CLRs are allowed.  All of the contributors probably use this tool frequently, so this speaks volumes to the function’s adoption and reuse.  One of the recent contributors, Eirikur Eiriksson, upped the ante significantly by writing a version that was even faster by utilizing a new SQL 2012 feature, and his diligence in constructing test harnesses is also worthy of honorable mention.

So here’s this huge group of peers (by that I mean SQL-folk of course), that have not only embraced Jeff’s work, but have also formed an on-going support community because they felt the underlying problem was so worthwhile that they should expend their precious time and effort on it.

There’s an interesting sidebar to my vote.  The Federal Relational Protection Agency (FRPA) might scoff at how preposterous the idea of a string-splitter being great T-SQL is.  After all, this is a relational database and we simply don’t store delimited strings in our properly normalized relational databases, now do we?  I guess what I’m saying here is that academic considerations probably take a back seat to adoption.  Clearly this problem occurs relatively frequently in the real world, regardless of what the FRPA would have us think.  And solving real world problems, to me at least, is really what it’s all about.

Aspirations

One day when I grow up I want to write a truly great snippet of T-SQL.  In my development career, knowing that solutions I’ve written are actually being used has always brought me great comfort and satisfaction.  No developer in their right mind, at least in my humble opinion, wants to knowingly build shelfware.  I’ve been around for a long time so maybe not everyone feels the same any more, but I hope this attitude still carries forward to all levels of developers currently putting their code out there for others to learn from and grow with.

So how about you?  Do you have a vote for the greatest snippet of T-SQL ever?  Post a link as a comment telling us why you think it qualifies, so all who see this might benefit from your particular experience with some T-SQL you think is great.  Affirmations of my vote are also welcome.

In the end, if this blog becomes a repository of some really great T-SQL code patterns, I will derive a lot of satisfaction from that too.

And to those of you out there that share my aspirations, whether or not the favorite tool/snippet you develop ever makes the list of all-time greats, wouldn’t you like to hear from all of those people that found your code wonderful enough to add to their T-SQL toolkit?  So take note you adopters out there!  If you’re using someone else’s code commonly to solve a problem, post them a thank you.  I’m sure they’ll be happy to hear that from you, and who knows?  Perhaps it will even spawn further greatness and sharing among one of the greatest communities the coding world has ever known.  That right, I mean the SQLverse, and we’re all a part of it!

Thanks for listening to my ruminations today on what makes T-SQL code great.

Follow me on Twitter: @DwainCSQL

© Copyright Dwain Camps 22 Apr 2015.  All rights reserved.