Most code I look at has been a disaster in the making for many years, worked on by many people who seemed to find one thing that sort of worked, and kept doing it.
I can’t lie: there are areas of my life where I do the same thing. SQL Server is the one area where I confidently know the rules, when I can break them, and explain reasons for both.
It’s not that I don’t make mistakes, it’s just that I learn well from them.
There are others things that I’ll do the same way for ages, thinking they’re needlessly complicated, before looking into less needlessly complicated ways of doing them.
What’s shocking is that sometimes there’s no better way of doing things.
Like xquery. What were they thinking?
Possibly the least helpful, but most humorous, way of leaving comments, is a large block of green text up at the top of a module.
There are all sorts of helpful insights buried in those comments to help me as a consultant understand my audience.
But… even though a lot of changes are described, there are often dozens to hundreds of queries in those stored procedures and function, or the view definition has 50 joins that join to 50 views that have 50 joins in them.
Fixed performance??? Avoided parameter sniffing??? Needed DISTINCT to fix a bug???
WHERE? WHICH QUERY?
SHOW ME TO YOUR MAGICK!
If you use a ticketing system, and there’s an issue number in your comment way up at the top of your stored procedure, add the ticket number in where you changed the code.
If there’s not, make one up. Give me some way to figure out where a change was made.
The only thing more frustrating is seeing some chunk of code quoted out, with no explanation of why it was quoted out.
In my stored procedures, I like to quote any code blocks as I go so they’re easier for me to find when I scroll through things.
Not because I can’t read my own code (okay, sometimes I get a lil’ lost), but because there’s often a lot of repetitive code, and it’s usually easier to read plain-English green text when I’m looking for something.
Here’s an example from sp_LogHunter:
This code isn’t hard to understand, but it’s a tiny chunk of code, and pretty easy to miss if I were looking for it.
Leaving little notes about even small sections of code can make life a lot easier for others trying to work for it.
Block It Off
While we’re on the subject, I must beg this of you: USE BLOCK QUOTES
Like I talk about in the post, using the
-- method can screw a lot of things up.
Depending on where you get the query from, and where the query originates, the whole query might end up on one line.
Having a bunch of double-dash comments makes it really hard to distinguish what’s code, what’s comment, and what’s quoted out code.
Worse, it breaks most every automatic code formatting tool out there.
They can’t figure it out either.
Comments in code are good for all sorts of things. Comments on the internet generally are mistakes and should be ignored.
Leaving comments not only helps others, but can really help you.
Think about how many times you’ve written code with a bunch of BEGIN/END blocks, and you get totally lost in which begin goes with which end and how many ends you need to stop getting an error at the very end of the stored procedure.
Heck, even while writing this post I went through a couple of my bits of code to add better comments in.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.