Attn Pls.
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.
Usually.
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?
Find Yourself
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.
Explain Yourself
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.
Tidbits
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!
Going Further
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.
Thank you, Erik. Great post. Wholeheartedly agree and especially on this point: “The only thing more frustrating is seeing some chunk of code quoted out, with no explanation of why it was quoted out.”
And what often happens as well is it gets unquoted…which can leave you feel like the day was a hurrican and you were caught right in the storm.
Thanks you for sharing your skills, knowledge and insights.
Heh, yeah. It’s a real pain. Either delete the code or leave a reason why it was quoted out.
I’ll admit I’ve used “–” for many of my comments, but I found it useful to put something like “BEGIN –Section1 that does such and such” … “END –Section1” when writing code. Swapping those out for block comments instead would make it a bit more portable, but the overall idea has helped me parse out different sections. Even putting that into the overall stored procedure of BEGIN and End (proc) helps to know that _this_ is the start/finish for the whole proc.
Small pattern, but that’s helped me. I do agree about the block comments, though. Now to see if there’s a way to make that a keyboard shortcut. 🙂
Heh, yeah, commenting begin and end blocks is really useful for your own sanity.
I often use IF blocks for commenting, code-collapsing, and debugging. Putting the same/similar comment immediately after the END block also helps for long procedures when you accidentally mess something up and have unbalanced BEGIN/END statements.
IF (‘get all the error logs’ != ”)
BEGIN
INSERT
#enum
(
archive,
log_date,
log_size
)
EXEC sys.sp_enumerrorlogs;
END /* get all the error logs */