September 26, 2013 at 1:30 am
How much detail should be used in comments, do you assume a level of understanding?
One comment I saw once was “If you need comments to understand this - you shouldn’t be reading it” – extreme to one side, on the other hand, do you really need a comment for
Insert into table_a
Select
b.col1,
c.col1,
b.col2,
c.col2
from tableb b inner join tablec c on . .. . .
The reason for asking - I am writing documentation for a few inherited databases, to me the current level of commenting is fine, it give an overview of what the stored procedure does, and if there is anything that seems outside of the overview is commented.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
September 26, 2013 at 1:44 am
I usually assume a basic understanding of SQL.
I'm not going to put a comment like "and now we insert the data into the dimension".
I explain the business logic used in the queries, why I do certain things and I explain the more complex SQL, such as for example using FOR XML PATH to concatenate queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 26, 2013 at 2:03 am
+1 to what Koen said.
I only explain logic and why I've done it a specific way.
I think it's safe to assume a basic understanding of sql - especially basic things like select's, inserts etc.
September 26, 2013 at 3:12 am
I think comments should state what you are doing and not how you are doing it or as I've seen it written sometimes, comments should specify your intent.
It is fair to assume that your code will be read or maintained by another developer who should know the syntax of the language you are working with.
There are, of course, those occasions when you might think it necessary to explain some really complex code but otherwise, as a developer I would much rather know what you set out to achieve with the code than anything else.
September 26, 2013 at 4:41 am
This is really down to opinion, but my feelings on it are, you should add comments for clarity.
If you're doing something really complex in T-SQL, tell us what it is. If there's a funky business requirement, let us know that. Clarify the code. That's all. I think it's fair to assume at least a fundamental level of knowledge of both the business and the code. But if you think it's possible that someone is going to be scratching their head, help them out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2013 at 4:52 am
Comments to state the why of the code, not the how. How I can read from the code. Why is important. Why is this being done, where does it fit into the overall app, that you can't get from reading the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2013 at 5:36 am
I assume basic SQL knowledge. At the start of most procedures, I state the reason for the procedure - why it exists in the first place. I then add comments for blocks of code explaining what it does, but just a description of the step. It's usually 1 or 2 lines long. Personally, I can't stand comments for every line, but that's just my preference.
There are times when more comments for a block of code are warranted to explain something that's little more complicated, but (as Grant said above) this is a matter of opinion. It all goes back to readability and maintainability of what you write. You may be the one maintaining it, but then again you might not be.
September 26, 2013 at 6:39 am
My comments vary. Usually we start off with the following:
/****************************
InstallDB: MyDB1
CreatedBy: Brandie
CreatedOn: 2012.09.26
Description: This proc pulls the orders made within the last week that were not
serviced within 24 hours.
Revision History
2013.09.26 / Brandie / Request 184
Changed date range on proc to include orders that were serviced within
5 minutes due to issues with closed orders that were never sent.
******************************/
And then inside the stored procedure, I make notes where WHERE conditions got changed or new columns were added or tables added / removed to JOINs. Mostly this is because we have a BU that changes their minds on what business rules they are using and why. These comments have saved us on numerous occasions where I've had to back out a single request that comes up every 6 months that the BU forgot was backed out because what they keep asking for doesn't work.
Also, the more complicated my queries get (numerous JOINs and subqueries), the more likely I am to stick a comment that says something like "Grab the quantity only when status is X, Y or D". Yes, I am explaining my code, but sometimes it does really get that convoluted and it makes it easier not only for other people to read it, but for me to remember why I wrote a particular CASE statement or a specific formula.
September 26, 2013 at 7:11 am
Thanks for all the replies. Glad to see that there is ageement
They say the devil is in the detail, now here is the situation with all the information. I have been commenting as most have been saying - example taken from production server:-
/*
Stuart 27/6/2012
This proc should update the employee table with any changes,
add any starters and flag the leavers
*/
Anything that I have had to look at more than once, I will put a "reminder" comment
-- only looking for "live" job codes
However, I have been told that this level of commenting does not provide enough information "for someone with my skill level" (direct quote from person tasking me with this project) to understand the code. I have been told that the following is the standard that I should be commenting as this (this is genuine production code):-
/*
/****** Declare the variables******/
DECLARE @Param1 VARCHAR(20), -- this holds the employees reference number
@Param2 INT -- this holds the employees department
@Param3 smalldatetime -- this will be the run time of the import
-- held against the record
/****** Remove any old entries from the staging table ready to import all
the current values******/
truncate table P_WBS_Actual_Key
*/
Someone with "my skill level" would know that the keyword declare leads to some variable being declared, the first comment is therefore redundant. Equally I would give the parameters meaningful names avoiding the need for the comment next to them. I would also like to think that the Truncate table statement should be self explanatory to any one who is let loose on a production SQL server. We have therefore less useful commenting in the version that is preferred :w00t:.
OK - rant over. It just grates with me having to conform to a pointless standard - the raison d'etre of the stored proc can be ignored and yet all comments are "good".
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
September 26, 2013 at 7:16 am
If I saw code like that, the developer would get a stern talking to...
DECLARE @EmployeeReferenceNumber VARCHAR(20),
@EmployeeDepartment INT
@ImportRunTime smalldatetime
Now I don't need comments to tell me what the variables do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 26, 2013 at 7:31 am
If only I could Gail - I would take Mr Shinai to them. Sadly they left before I started here.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
September 26, 2013 at 7:33 am
At risk of starting another rant, if you're already updating the stored procedure, variables are easy enough to rename.
September 26, 2013 at 7:44 am
I would imagine renaming variables might be dependent on if they have been referenced by name in a front end somewhere.
Either way, that's certainly interesting code.
How is @Param1 then tracked through the Procedure and what stops the coder from using the wrong variable somewhere...
The issue with this type of commenting, as I see it, is that it is assumed that the comments will be updated whenever code is changed but in a lot of instances that just doesn't happen so you could end up with comments that are more misleading than they are descriptive of what the code does.
September 26, 2013 at 7:45 am
OTF (9/26/2013)
I would imagine renaming variables might be dependent on if they have been referenced by name in a front end somewhere.
They're variables, not parameters, so can only be referred to within the procedure.[/quote]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply