All About SQL Server Stored Procedures: Comments
Video Summary
In this video, I dive into the world of commenting in SQL Server stored procedures, highlighting why good comments are crucial for maintaining code quality and understanding over time. I share my frustration with seeing large blocks of commented-out code at the top of modules, which often lack context or detail about what changes were made and when. To address this, I emphasize the importance of using block quotes instead of double dash comments, explaining how they allow for more readable and maintainable code. I also discuss the value of integrating ticketing systems into your comments to track issues, fixes, and enhancements, ensuring that future developers (or even past you) can easily follow the evolution of the code.
Full Transcript
Erik Darling here with Darling Data. Feeling fresh, fresh as a flower today. We’re going to do the second video in our traipse into store procedures. And in this video we are going to talk about the almighty comment. Because there is just not enough actual commenting in code that I look at. Most of the what looks like comments in the code that I look at is actually just commented out code. Which is somewhat strange because there’s no comments about why the code is commented out. There’s just nothing. You’re just left to guess. You’re just left dangling in the breeze trying to figure out just what exactly happened to that code. That made it no longer necessary. Vestigial code. So anyway, that’ll be today’s fun. Before we get into that, of course, let’s talk about how you and I can get along better. If you would like to support the efforts of this channel to bring you such high quality SQL Server content, well, golly and gosh, there is a link right down in the video description where you can click and you can offer me. I believe the starter package is $4 a month for this. You can join the 55 or so other folks who have chosen to help me pay off my camera and this microphone and this Adidas shirt and a whole bunch of other stuff around me that you can’t see like lights, electricity, I don’t know, stuff. If you don’t have $4 a month, that’s totally cool. I understand the demands and rigors of the modern world are quite difficult. I’m not going to lecture you about avocado toast and all that, but it is what it is. You can do all sorts of free things to support the glory of Darling Data. You can like, you can comment, you can subscribe. And if you would like to ask questions to feed into my infinite, what do you call it, material loop? I don’t know. I’m actually not even sure what they call it. It’s a strange thing. You can go to this link, which is also in the video description and you can ask me questions and I can answer them here, which is a pretty decent way of getting more information out of me.
If you need help with SQL Server, if your company has some extra money sitting around it. It is the beginning of the year, as far as I know, there’s all sorts of beginning of the year budgets and whatnot. And if you need consulting help with SQL Server, well, once again, BeerGut Magazine has said that I am the best SQL Server consultant in the world outside of New Zealand. 15 years in a while. No, actually, I don’t even know. Anyway, health checks, performance analysis, hands-on tuning, performance emergencies, developer training. Hoo boy, I do all sorts of fun stuff. And I look just this good doing it. If you would like some very high quality, very low cost SQL Server training content, you can get all 24 plus hours of mine for 75% off. It’s about 150 USD. And that will last for life.
Your life. Your life. My life, I’m not sure. We’ll see what happens there. Upcoming events, of course, SQL Saturday, New York City is coming your way May the 10th. There is a pre-con as well on May the 9th. And God help you if you don’t go to either one. We are no longer friends. We’ll no longer ban you from my channel or something. So with that stuff out of the way, let’s talk a little bit about commenting code. Now, let’s see if ZoomIt will, how many clicks it will take for ZoomIt to Zoom. This was three clicks before ZoomIt responded and Zoomed. Possibly the least helpful but most humorous way of leaving comments is the big block of green text up at the top of a module. It doesn’t matter if it’s a store procedure, a function, a view. I don’t know. Whatever else.
Just a big hunk of green text. People just writing stuff away. Initials. A little comment. But without anything interspersed within the code to like point out where these things changed and what happened. And maybe even what things used to look like. And no linking artifact to more information. Now, I do realize that a lot of SQL code was written before the days of really good bug tracking system things. And there weren’t always like Jira’s and bit buckets and whatever else people use for stuff like this. I’m still not sure what a Trello board is, but I still hear people talking about them.
So I understand that not everything is linkable back to the beginning of when these things were created. But new issues, whether they’re performance, logic, bugs, maintenance, fixes, additions to code. It’s really helpful to have, you know, like you don’t have to put the store procedures life story in the store procedure. But it is it is helpful to have like some background somewhere else that’s a little bit more verbose.
Right. It’s good to note that something changed and like like some way to figure out more about that change. Because a lot of the stuff that I see is someone just says stuff like this. Fixed performance, avoided parameter sniffing, added distinct.
Doesn’t tell you which query because as far as you know, like every query has distinct in it. So you’re like, OK, well, you add 50 distincts. I don’t know. So if so, if you have a ticketing system, only took Zoom in four clicks to do anything that time and there’s an issue number, you may want to add that to your code comments because that makes life a lot easier.
If there’s not, then you need to figure out some other way to make sure that the changes or additions or fixes you make to your code is sort of trackable in some way. Not everyone is going to want to dig through like GitHub or get blame or whatever. Every single time they want to track down what exactly happened when you made a change.
So we’re going to talk through a little bit of that stuff. We’re going to talk through how you can be nicer with those things. But one thing that I’m going to say straight off the bat here is please use block quotes.
Do not use double dash quotes. Double dash quotes are just terribly confusing, arduous, and no one likes them. Let’s look at a couple reasons why, right?
So the reason why I want you to always use block comments is because depending on where the code comes from, right? Like there are some places where you can copy and paste code from or like, you know, you might end up like, you know, someone might send you code in some way. And the problem with the double dash codes is that once that double dash hits, it quotes out everything else.
Right? Right? When you use block quotes, you can put a comment in line like this, but still have the query be runnable. Of course, this query is not runnable.
It’s just select star. Must be, gosh, SQL Server. Thank you. We must specify a table to select from. We sure must. We sure, sure must.
But with this, this commenting system, we can just, we get exactly what we want. The other thing that I run into a lot is like the sort of unformatted code problem. So like, like one, actually one, one of my biggest pet peeves is SP help text because it’s really easy and convenient to like hit SP help text, get some code printed out for you.
But SP help text does not do the best with like new line printing and like line wrap stuff. There’s a lot like, there’s a definite like limit to how much SP help text can print on a single line. And like, you know, you always end up with one of those.
And I think, I think it handles tabs and spaces real poorly, but we’re going to talk about tabs and spaces in the next one. Where you end up with like partial lines of stuff and you’re like, you go to like hit F5 to recreate the procedure or something. And there’s just like this wall of errors because like things ended up on the wrong lines.
But like the big thing for me with the commenting is let’s say that, you know, there was a comment like this, right? Where like, you know, you have part of your query up here and like, you know, we could pretend this all came in on one line and I just move stuff down so you can actually see it on the screen here. But like, let’s say there’s a comment like this.
All it says is removed. All right. And you don’t know if the removed applies to this or this and this. All right.
So you’re left with this like this logic puzzle where it’s like, well, which query is actually correct? Whereas if you use block quotes and you say something like this, you still see that this part of the where clause is valid. Right.
So like another reason why quote style makes a big difference is because you can you make very clear which portion of the code the comment applies to. It’s not always going to be apparent if you just put it on the line above or below because no one’s actually going to know like like unless you’re very, very descriptive, you’re no one’s going to know what what exactly the comment applies to. So the comments that I see quite a bit and I do have to thank an LLM for this because it made it very easy to ask something else to generate crappy comments and may not have to sit there and make updates and initials and type a bunch of useless stuff.
This is again humorous uses of LLMs are about the only good use of LLMs making pictures, having it do dumb, dumb tasks like this. LLMs are still terribly on serious things for anything, anything serious you have to do. Unserious things.
Beautiful. Keep it going, fellas. Unserious things. So like this is the kind of stuff that I always see, right? Like bug fixes, performance improvements, made it better, minor tweaks, added this, changed some things. These are the kind of comments that I generally see in store procedures that I work on and I’m like, well, guess what?
Guess what? I have no clue what any of this means. Like is this like, okay, like where did this happen?
And then like, you know, you go through the code and there’s some, some crappy comments like this. And it’s like, ah, well, thanks. But okay. Coming back up here. Did any of these fixes apply to this query or this query or this query?
Like where did, where did, where did they apply to? We don’t know. We don’t know where anything happened in here. We have absolutely no way of ascertaining where any of the stuff that changed up at the top applied down below.
And it just becomes very confusing. And because nothing, like there’s nothing readable about that. It’s not helping anyone figure anything out.
The way that you generally want to do this stuff is, you know, if you have a ticketing system, is you want that ticketing system, you want to put items from that ticketing system in the comment. And you want to have more information available in the ticketing system, right?
Again, more verbose information there. So that if like anyone’s looking through this and they really need to figure something out, they can go read that.
And they can, you can put the old code in there. And you can, you know, only keep the new code in the procedure so that you don’t have big blocks of coded out code everywhere. So like, this is a much better way of doing things. And like, when you get down into stuff, you can even put things in here like this.
Like, you know, like whenever you make, like all the changes that apply to a specific section, right? Like this stuff, query one, we did these things to it. It makes a lot of sense.
We added a covering index. Yay. We fixed a calculation. We optimize. Okay. Well, okay. So this is where LLMs do get a little cracky, right? Because anyone who says stuff like this is full of malarkey, right?
Like uses, like the optimizer is going to arm wrestle you on joint order no matter what. But so LLMs, again, still terribly unserious beings in the world, but funny anyway, right? We all can get a good chuckle out of that.
But like stuff like this is much, much more helpful because again, people can plug these things into whatever ticketing system you use. They can read up about exactly what happened and when, assuming that whoever made the change was not just doing something randomly at like four in the morning and like didn’t say anything about it. It was just like, damn it, I’m fixing this and going back to bed.
So this is a much, much better way of doing stuff, right? Because you get the best of all worlds. You get like detailed, well, you get like exactly which things, which fixes apply to which query. And then when you go to the item, you can see which, like more detail about what those fixes entailed.
So that’s a much, much better way of doing stuff. So if your code commenting looks like the previous example, where it’s just like some dash dash stuff with like, you know, some, you know, a little bit of typing mixed in, like again, fixed bugs, improve this, you know, performance, parameter sniffing, whatever it is. This is a much better way of doing things.
It’s going to help not only you understand code better when you come back to it like months or weeks or even maybe even years later. It’ll help anyone else who comes along and needs to work on this crap later deal with the, you know, gigantic, incredibly complicated code base that you’ve generated over the years for yourselves. So when I’m, these are the things that I look for in good commenting, right?
Again, block quotes, very important, right? Double dash quotes are awful. Block quotes are way better.
Some kind of way to figure out which fixes from the big block of change log stuff up at the top apply to which queries or which parts of the store procedure or view or function or whatever it is. And then some way to get more information, some more background on exactly what the problem was, what the change was. And like, like maybe even like the previous state of the query before your changes went in so that if someone’s like, well, oh no, I think it should be like this.
They might go look at that previous version of the query and be like, oh, it used to be like that. And that’s what didn’t work, right? So there’s all sorts of good stuff that you can convey with like the more information ticketing system stuff that you probably don’t want to just stick in the store procedure because you don’t want like, like 4,000 line store procedures that are 3,000 lines of comments and 1,000 lines of code.
That’s not a very good situation either. So anyway, that’s, that’s, that’s my take on it. Um, I’m, I’m not a DevOps guy.
I’m not a CI, CD guy. I don’t, I am not a, not a, not a professional developer in that sense, but, um, I do, I do see pretty clearly when, which, when, when people have embraced good commenting and ticketing systems. And when they have not, because, uh, when, when they’ve done a good job, my job gets easier.
When they’ve done a bad job, my job gets harder. So anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. I hope that you will comment responsibly in the future. And I will see you in the next video where we’re going to talk about code formatting. And, um, some of you will become best friends and some of you will become enemies.
Lifelong enemies. And that’s fine with me. We can’t, we can’t, we can’t all be friends forever. It just doesn’t work that way.
And code formatting is the easiest way to gain and lose friends. Right? Build allegiances and make sworn enemies. So that’s what we’re going to do.
Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.