Hate to be argumentative, but such zen code is the sound of one hand clapping...
Not as good at commenting my code as I would like, but working on it.
I have seen commented code where the comments just got in the way and provided no valuable information beyond what was already there in the code itself. For example:
-- set @Debug = 1
set @Debug = 1;
Yes, I have seen this. What in the world does the comment add to this piece of the code.
Describe what the procedure/script/trigger/function/whatever is expected to do, provide a history of changes and way they were made, you may even include some simple test code to run to verify the code works. Just make sure if the code changes that the test code changes as needed as well.
The code, however, should also be considered part of the documentation. Use variables names that make sense. Use table aliases and use those aliases to ensure that people can tell what columns come from what table in multi table queries. Actually, using them in single table queries. You never know when you may have to add a table to a query. Use white space to make the code human readable. Use indenting to help clarify blocks of code. In ORDER BY clauses use column names, not ordinal position to order data. If you have a complex computation, explain it as close to the computation as you can.
All things that I don't always do. Need to be sure I start doing so I can quit being a hypocrite (Do as I say, not as I do) with regarding to coding. Some people are just better at it than others, either they just find it easy to do or they have been hit with enough pork chops to get the idea. It is a habit that one has to work at until it becomes second nature. I find I tend to comment code that is not as intuitive as other code I write. Should comment it all.
Code I have been writing at work, I have started adding a nice preamble that I include the name of the proc/function/script/trigger/etc, creation date, author, site (where I was working at the time I wrote it such as ISAF HQ, Bagram AB, Corporate HQ). I try to describe what the code is supposed to accomplish, the requirements the code is supposed to meet. And then I create a Change History log for capturing changes to the code. I have gotten good about the code I put in production, not so much for one offs or code I post on SSC.