Spaces
First, a list of things that are horrible:
- Tabs
- Leading commas
- All lowercase
- All uppercase
- Not putting new things on new lines (from join on where and or order by group by)
- Not indenting things
- Leaving AS out of table aliases
- Leaving out column aliases
- Top expressions without parentheses
- Not aligning parentheses
There are others, but I’m tired of thinking about things that are demonstrably wrong to do, like putting ice in scotch or sugar in wine.
Thank you for complying.
Not All Code
When you think about formatting code, you probably think primarily of organizing a single query so that it’s inoffensive to civilized society.
But you should also apply this to your code as a whole, too. People who use words wrong will call this “holistic”.
- Create all your temporary objects in the same section of code
- Declare all your variables in the same section of code
- Set as many of your variables as you can in the same block of code
- Do as much of your validation in the same section of code as you can
Few things (aside from the list at the beginning of the post) are more grating than seeing random things being created and declared at random points in the code.
It makes things far more difficult to scope, follow, and understand.
I’ll lend some leniency to using SELECT INTO here, because sometimes that’s the best way to get a fully parallel insert.
I’ll also lend some leniency to cursors, because sometimes it’s only sensible to declare them when they’ll be used.
Consistency Is Key
Formatting should follow a consistent set of (my) rules. It is sometimes possible to get used to inferior style choices as long as long as they’re predictable.
What’s impossible to get used to is code formatting that’s constantly changing gears.
There are many ways to format code out there in the world. Some are more acceptable than others.
For example, some companies may shy away from online code formatting tools because who knows who might see the code.
So much valuable intellectual property exists in your “joins” and “column names” and such. Someone else may steal the bright idea to also key on “identifiers”.
I personally use SQL Prompt, because it gets me about 80% of the way to how I want code to look.
While I do wish there were more ways to implement single formatting rules, like changing how columns are aliased from the shabby “column AS new_name” to the correct “new_name = column” format, it’s often trivial to tweak certain things after applying everything.
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.
No argument here, just curious… Can you expand on why leading commas are horrible?
Yes, because it’s wrong.
💯
…but seriously, the only reason I usually hear given for leading commas is “to make it easier to comment out lines when debugging”, which IMO is basically setting yourself up to fail/not having faith in your code; and not putting, say, *readability* first.
I also heartily dislike folx throwing leading semicolons in before CTEs etc. rather than just terminating everything properly and understanding the actual syntax of the language they’re writing. You should always code safely so that the lowest common denominators (i.e. people) have a reduced chance of screwing things up, but you should also gently force them to learn what they’re actually doing.
Well, yes, it’s fine to make certain things easier in prototype code. For example, it may be easier to type tabs to more quickly align things, and then replace them with four spaces later. It may also be easier to put commas first to get the initial query right.
But those things should not be in the final product. In my view, it’s like wearing a suit that still has chalk lines on it and pins in the pant legs.
Leading commas saves storage space, since they replace a leading space (I hope you intend your code), while trailing commas will be added to the end of the line.
And it is much easier to align leading commas (below the T from SELECT) instead at aligning them on the end of a line, since the columns / aliases have usually a variable length.
And of course it is easier to comment out with leading commas (since you comment out much often comment the last than the first line) and regarding readability – it is just what you are used to see / read.
BTW: if you vote for trailing commas you would need to apply the AND at the end of the line too following the same “rule”/logic. And 98% would agree, that this is really horrible.
HAHAHA, storage space. You people.
Totally agree with all your above gripes. Don’t forget the with nolocks to the naughty list :^)
That’s for another post 😉
You are so wrong about Tabs!
Sql Prompt is awesome. Snippets save me so much time.
No, tabs are the most frustrating thing to deal with.
I’ve come around on tabs in my advanced age. Like… say that you prefer a four space indent and I prefer two. If the document uses tabs, we can both have what we want by using the editor’s “display tabs as «this many» spaces” option.
That is patently absurd.
TABS in code (for formatting) or horrible for exactly the reason you listed. When TAB size preferences differ, you get people hitting TAB completely different number of times to get things to line up the same way and then, when viewing with a different TAB size, the alignment is completely borked.
If you’re on a TEAM of developers, you need to standardize on a size and then everyone has to live with it. If you’re a lone wolf, do whatever you want.
I liked tabs until I realized pasting code into almost anything else messes up the formatting. Four spaces is the way to go to keep the formatting pretty and SSMS does those for you if you turn the option on to convert tabs to four spaces. Easy peasy 😀
You are my favorite commenter of all time.
Less is better. 2 spaces please. 😉
As long as they’re spaces, I’m cool with any even number less than 5, ha ha ha.
we are no longer working on a AS/400 with Cobol (I did this and there WAS a 2 spaces intend rule, since a line must not be longer that 80 chars because much earlier this was what you could see on screen / printer / punch tape machine)
I love every time Erik posts about code formatting. I picture him sitting there with a scotch in hand while posting the article thinking to himself, “today, I shall choose violence”. I come here for the solid sql server content but stay for the comments for posts such as this. For me, the most important thing in sql server formatting, as stated in the post, “Consistency Is Key”. In a larger team of sql devs, If I can get people to care less about how many lines (no formatting, minimal lines of code) and more about readability and consistent formatting I’m happy enough.
Heh heh heh, you’re not entirely wrong!
And yeah, good point about herding cats there. I think that’s why tooling to automatically format things is so valuable.
Everyone gets the same settings file, and only has to hit a couple buttons to fix their spaghetti code.
Thanks!
Ah, when it comes to code formatting, everything is religious, and peace was never an option. I especially like Erik’s closed mind on this topic, since I agree with him on everything (except “new_name = column”, where he is clearly wrong, ha ha).
Quite : “new_name = column” looks like assigment. Moreover the AS keyword isn’t there to be highlighted in SSMS.
Yes, it’s much easier to follow what ends up as what and where that way.
I find it much easier to have the column name first in the assignment, especially when the expression being aliased is long and complicated.
In addition to not aligning parentheses, overuse of parentheses is a common one I see and can make code harder to read.
Obviously, all those parentheses are the correct way to do it. Afterall, that’s how it comes out when you tell SQL Server to script something for you.
Thankfully, SQL Prompt will also take care of reformatting to use the “new_name = column” aliasing.
Love the discussion. In addition to the principles like consistency, one I promote is the notion of minimizing the cosmetic difference between each line of code. This naturally aids the eye/brain to see the substantive difference, instead of having to (sub-consciously, for the experienced programmer) filter then ignore all irrelevant differences. For this reason, alignment is generally a good thing, and is why I totally agree on the “=” assignment rather than “AS” approach. It also is a reason to use CONVERT instead of CAST. And, for me, is also the reason I use the leading comma on a vertical list (when I need to have a vertical list.)
Perhaps the most refreshing part of this though, was that I thought I was alone on spaces over tabs and had just figured I’d better keep silent about that. 🙂
Yes, that’s a great point about minimizing cosmetic differences. I wish I had thought of that 😃
Thank you for addressing the logical flow around building a proper procedure. I work in the ERP space and routinely deal with multi thousand line procs and defining things in blocks improves readability and understanding.
Special shout out to you developers who don’t know where the farging carriage return key lives.
I’m a big fan of SQL Prompt since I can quickly format to “my style” for dev and understand and then flip to “corp std” when checking in the finished product.
Ooh, yeah, every time I consult for someone using an ERP, I wish I had doubled the price.
Clearly you are all a bunch of “SQL Only” guys. Many of these are in complete opposition to general coding standards such as “variables should be declared where they are used not at the top of the method/function.” SQL devs also don’t understand the principle of SOLID code – “I work in the ERP space and routinely deal with multi thousand line procs…” There’s no way something that is thousands of lines long is doing one thing, which is good coding practice, no matter the environment. You guys complain about leading commas making it less readable and harder to debug then pack so much stuff in a single sproc that nobody but the original developer, and even then only for a short period of time, could possibly understand what all is being accomplished much less debug it when it has a problem.
SQL Prompt – $200/year to format my SQL code. This subscription model pricing is out of control. SQL prompt is a tool like a hammer. It should be a one time cost. Yeah, I know, I can pay once and not get the upgrades but then when Erik changes his mind about how to format SQL code, my hammer will be the wrong hammer for hammering out good looking code.
Fortunately, for me, retirement is just around the corner.
Ed — the title of the blog post is “The Art Of The SQL Server Stored Procedure”. Thanks for reading 😂
re: “new_name = column”
I love this SQL syntax column formatting and SQL Prompt is awesome, but only in T-SQL!
I find it a mission if I copy this SQL text to BigQuery, convert all the table names, cater for a few syntax changes (e.g. change the ISNULL to IFNULLs etc.), and then embark on changing “new_name = column” to the shabby “column AS new_name” because BigQuery can’t read this format.
HAHAHA, oh that’s annoying. I would lose my mind.
Who the hell puts sugar in wine?
Please send them to me for correction!
I think it’s a Long Island thing.
Nice post, I was itching for a fight today. Leading commas are superior in every way. I have a preference for tabs but not a strong one. Surprisingly, I agree on all other remarks. new_column = column is so much easier to read and should be mandatory on all inserts. Especially when you are computing that column. There is nothing worse than debugging an insert with 100 columns and having to count the columns to try and figured out which column in the select is breaking the insert.
Fight kinda fizzled a bit there.