June 30, 2009 at 3:46 pm
Andy Lennon (6/30/2009)
i'm terrible with documentation. i put descriptions into procedures in a comment block up top and have additional brief descriptions for the business functionality of discrret sections within them. i don't think i've ever commented a table (never even occured to me) and have certainly never written up anything like a comprehensive document for overall database structure. I'd like to get in the habit, since on more than one occassion i've been asked to review code i or a co-worker wrote 2 years before to explain why it's suddenly "not working" (bollocks, of course).Unfortunately, client-side pressures and my boss being even worse about documentation/commenting than i am conspire to prevent such sensible actions. : /
This is what I tend to do too, put comments in targeted areas where I imagine someone will need to know what I was thinking and doing. Often that person later on is me. It's like "what was I thinking!? 😀
Our group has also strayed from writing formal documents for programming procedures or database structure. We do have what's termed "approach papers", but those are often written by our business/technical liaisons.
Ken
June 30, 2009 at 3:51 pm
There's precious little to justify lack of complete documentation.
I don't think that my position is that different from yours. Our disagreement is mostly over where things should be documented and how verbose that documentation should be. From my experience, some moderation is helpful. longer better
To that point, the topic of this discussion is the header.
The documentation helps a person go through code faster when doing maintenance. Of course, reading the documentation does not substitute for checking against the code. So, you read the documentation, go much faster through the code than you would without documentation, and you see a contradiction. Good. A disconnect between documentation and code can be helpful in a couple of ways, including helping you zero in on the problem at hand.
As I said in my earlier post, the place to explain things that the code doesn't tell you is in the body comments (preferably as close as possible to the code it's describing). That way if you make a change, it's easy to see what comments need to be updated. Much easier than reading through a large volume of comments at the top of the sp, class, whatever.
I also stand by my assertion that details about the front end implantation (who's using it and why they're using it) do not belong in the database code at all. If you have found this system to be effective where you've worked then so be it. I have not.
And finally, incorrect comments in the header are a bigger problem than you’re admitting. I might have been a bit hyperbolic in saying that they're worse than no comments, but when I read the comments I'm looking for an answer (what does this code do, what has changed in the last year, etc). What I’m saying is that I'd rather have no answer than the wrong one.
For the record, my sp header's description answer's just two questions: "What does this thing do?" and if it returns anything "What does it return?". This never takes me more than a couple of sentences at most. Below that it may include a list describing any input and output parameters. I say may, because I do not always include it. If I have a stored procedure named GetSerialNumberInfo and it only has one input parameter named serialNumber I don't think it needs to be explained in depth. Blasphemy. I know.
For updates, as I said: name, date of revision, and what the revision was.
June 30, 2009 at 4:19 pm
I don't think that my position is that different from yours.
With all due respect, I think our positions are very, very different and not just a matter of degree or location. If you think our posts don't sound all that different, I have done a poor job of communication (which is really bad considering the topic of these posts). But I have to get some work done today...
longer better
You will recall that I made a big point of saying that the documentation should be complete, not that it should be long. I do not equate longer with better. Neither do I equate shorter with better. I equate adequately documenting with better. Sometimes that can be done in a single sentence in the header and practically nothing else in the body of the proc. Sometimes it takes a couple of pages in the header and lots more words (pages/paragraphs) by each section of the body of the proc or query. The point is to professionally document one's code, not to worry about length requirements in either direction.
... [header] may include a list describing any input and output parameters. I say may, because ... If I have a stored procedure named GetSerialNumberInfo and it only has one input parameter named serialNumber I don't think it needs to be explained in depth.
On that we do agree, and it is a perfect example of my point in the paragraph above. Sometimes there isn't much to say, especially on the easy question of "what".
You'd never guess I was passionate about this subject or anything. (grin)
June 30, 2009 at 4:53 pm
Great idea.
I use Doxygen for generating documentation for C++ code. It's got a rich set of features and is free. Something similar for database procedures/functions would be very useful and would define a standard. Of course, we'd like it to integrate into SQLSMS, provide version managment features ...
June 30, 2009 at 4:53 pm
With all due respect, I think our positions are very, very different
Darn it. I was trying to couch my criticism in alliance. Many times I can sometimes get a person to agree with me by either letting them feel that we agreed all along or a least giving them an out if they change their opinion without forcing them to say they changed their opinion. Though, like the Jedi mind trick, it only works on lesser minds. Touché.
You'd never guess I was passionate about this subject or anything. (grin)
Some of the most fun discussions I ever had at work center around the details of "how we do things". I chock this up to being a profession of perfectionists. Years ago I was in a meeting on approving the final draft of coding standards that ate up nearly the full allotted hour discussing whether the { should come at the end of the line or on the next line below.
P.S. I'm a next line guy, and proud of it. It's not just a matter of style. Putting it at the end of the line causes physical pain in my eyes!
June 30, 2009 at 7:28 pm
The way we document database objects as well as any comments of SQL code changes is something we have recently changed at our organisation. I won't go into what we used to do, although our old ways were still sufficient. What we do now is this:
All code changes are handled within our source control when the check in occurs; it saves the development team from doubling up on check in comments as well as updating massive headers residing in our stored procedures and functions and so forth, although explanations for revisions are stored in our source control, a header is still used for all our database objects within the script.
Where our massive improvements regarding database documentation have come from, is the use of extended properties. We now add extended properties for these following objects: Tables, Table columns, tored Procedures, Stored procedure parameters, Functions and Function parameters. Using extended properties is not the a massive improvement, but rather the use of the application ApexSQL Doc to compile a .chm file of the database. It encorporates all objects within the database as well as displaying the appropriate extended properties within it. All objects are hyperlinked and dependencies are also displayed with links.
When an object is modified, the extened property is also immediately updated within the database, and then it's simple to have the .chm file updated by compiling a new one whenever you deem necessary. We schedule a job to run the ApexSQL Doc projects nightly against all our dev and test databases using the application FinalBuilder. Therefore the most relevant objects within our database have updated documentation in an easy to read and navigational format.
In summary, all explanation of changes are documented in our source control, but up to date object descriptions are kept within extended properties and viewed using a .chm file compiled by Apex SQL Doc.
Hope this post can be seen as useful for someone.
June 30, 2009 at 8:45 pm
Heh... I agree... we need automatic forced headers and automated code reviewers for things like "Warning: It's been determined that the code windows on SSC don't work correctly in IE" and then have the machine refuse to promote the code even if it breaks someone's schedule. 😉
On the serious side, we already have a plethora of standards about headers and documentation that no one follows. We don't need another one. Take X-Docs in Java as an example. There are two types of people who use X-Docs... those that actually care about quality code and those forced to use it by company standard. No standard and no code to force a standard will turn that second group around to see the light. In fact, that second group will frequently resort to mere malicious compliance to meet the minimum automated requirement. The only way to stop that malicious compliance is to do a Mark 1 Mod 1 Eyeball test, otherwise known as a human code review.
I agree with Blandry... proper embedded documentation and proper headers are a must. But, whether an X-Doc-like ability exists in SQL Server or not, someone still has to describe what the stored procedure does or what a table is for. Automated or not, automatically enforced or not, that's still going to take the same amount of time to do and those opposed to such documentation will still use malicious descriptions like "I don't actually care what this code does" which will meet someone's minimum programmatically enforced idea of what a description should be. Like I said, only the human touch of a code review will make any of those efforts worthwhile.
Finally, even if the header is somehow done correctly, a real pretty header on a bad piece of code is like putting a Cadillac grill on a go-kart with a flat tire and no gas. 😉 Let's concentrate on the real problems of writing quality code.
--Jeff Moden
July 6, 2009 at 3:58 pm
I have been a firm believer in properly documented code for a long time. Most of the points I would have added have been covered by blandry's post so no point to rehash them.
What I will add is this: when I was looking around for SQL documentation generator tools I found tools to automatically document basic structure but offering no way to supplement that with custom doc-comments thoroughly describing the use of the code. I want full-fledged API documentation, something I could come back to 6 months later and see exactly how to use the code *without* having to read the code.
I had previously developed an XML-to-XML conversion engine that I used as a pre-processor for generating pages on my web site. I whipped up a new configuration file, instrumented my SQL files with XML doc-comments a la C#, and now have a SQL API documentation generator. It is *not* the greatest thing since sliced bread but it does a reasonable job for me. Perhaps some of you may find some benefit in it too. The "user guide" for this is an article I wrote for devx.com last year entitled "Add Custom XML Documentation Capability To Your SQL Code" at
July 8, 2009 at 7:27 am
cameron.wells (6/30/2009)
Where our massive improvements regarding database documentation have come from, is the use of extended properties. We now add extended properties for these following objects: Tables, Table columns, tored Procedures, Stored procedure parameters, Functions and Function parameters.
Isn't the problem with extended properties is that they're fairly cumbersome to set using sp_addextendedproperty and updateextendedproperty, and even more so by drilling down to the object's properties in Object Explorer? Ideally commenting should be done during the course of coding, not after the event. I'm not sure that extended properties as currently implemented in SQL Server and Management Studio lend themselves well to this. And even once they're set, unlike comments, they're only visible once you've separately generated html documentation using third party tools, rather than being visible in and around the code of the object you're working on in Management Studio, which is why inline comments are arguably far more useful.
Thoughts anyone?
David
July 8, 2009 at 7:42 am
The extended properties work OK for documentation -- if EM/SSMS is your documentation framework. ::cringing:: :ermm:
They are a pain to enter, but there are tools out there that read them when they build reports. I've used dbdesc from Logica a few times and it's not bad at building basic documentation on a database.
Andrew
--Andrew
July 8, 2009 at 7:46 am
@michael-2 - I like the output, but the documentation syntax itself is so verbose! It looks like 60% of the time you spend typing a procedure could easily encoding (not writing) your documentation.
Andrew
--Andrew
July 8, 2009 at 8:36 am
For me, extended properties can be only part of the solution. Much of the information we want to put into headers are lists. We want lists of revisions, parameters maybe, and use examples. you are limited to one of each type of extended property for any database object.
Obviously, we need extended properties to store the (structured) comments for codeless database objects (table scripts aren't stored) but they aren't in themselves, sufficient. The scripts for adding a range of extended properties can get very bulky.
Best wishes,
Phil Factor
July 8, 2009 at 8:43 am
David Atkinson (7/8/2009)
cameron.wells (6/30/2009)
Where our massive improvements regarding database documentation have come from, is the use of extended properties. We now add extended properties for these following objects: Tables, Table columns, tored Procedures, Stored procedure parameters, Functions and Function parameters.Isn't the problem with extended properties is that they're fairly cumbersome to set using sp_addextendedproperty and updateextendedproperty, and even more so by drilling down to the object's properties in Object Explorer? Ideally commenting should be done during the course of coding, not after the event. I'm not sure that extended properties as currently implemented in SQL Server and Management Studio lend themselves well to this. And even once they're set, unlike comments, they're only visible once you've separately generated html documentation using third party tools, rather than being visible in and around the code of the object you're working on in Management Studio, which is why inline comments are arguably far more useful.
Thoughts anyone?
David
We used to add a table to the database just to hold this type of data. My simple documentation printer prepares an rtf file from the column specifications and that table. Then I found Extended Properties. We have been filling in the Description property in SSMS. :pinch: I modified my printer to pick that up as well. I found that you can put these descriptions at the table level.
Would it help if there were a better tool to manage these descriptions? Would it be worth $9.95 a copy?
ATBCharles Kincaid
July 8, 2009 at 9:13 am
Charles Kincaid (7/8/2009)
We used to add a table to the database just to hold this type of data. My simple documentation printer prepares an rtf file from the column specifications and that table. Then I found Extended Properties. We have been filling in the Description property in SSMS. :pinch: I modified my printer to pick that up as well. I found that you can put these descriptions at the table level.
Would it help if there were a better tool to manage these descriptions? Would it be worth $9.95 a copy?
What does 'manage' mean for you? Assuming the best place to manage such descriptions are in SSMS as this is where the code gets written, what would (in an ideal world) be the ideal way of entering, editing and viewing extended properties? About a year ago I had a brief conversation with a Microsoft SQL Server support engineer who conceded that extended properties failed to properly catch on because they simply hadn't been adequately exposed to the user. Unfortunately he didn't have a view on what the actualy solution should have been.
Is there an obvious solution?
David
July 8, 2009 at 9:32 am
Sorry David. I'm not going to try to mess with SSMS.
I am working on a tool that lets me connect to the database, shows me a list of tables, I choose one, it shows me a grid of column names, data types, and descriptions. I can edit the descriptions in the grid and them save them to the extended properties.
Not ideal, admittedly. But better after the fact then not at all.
ATBCharles Kincaid
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply