Formatting Code

Many people like to argue about how to format code, when to use tabs or spaces and other general formatting rules. I’ve worked with people who “enjoy” spending time formatting other people’s code and will often spend a lot of time on this before they get to making changes or whatever they are really supposed to be doing. I say enjoy because they seemed to take a perverse pleasure in aligning the code to their liking. I’m all for arrant pedantic OCD behaviour but it really can waste a lot of time.

I don’t care either way in the tabs vs spaces argument but to be fair there is something to be said about being able to easily read and therefore understand someone else’s or even your own code. It’s also annoying when for example you develop some code in one IDE and they copy and paste it to another environment only to lose all of the formatting applied.

I’ve been writing a lot of MDX in the last year. When I first started using the visual tool in SSRS to construct MDX queries I quickly came to the point where they needed to be edited manually and I noticed that it puts a comma before a cell or tuple in the code listing. In SQL I’d always put the comma after but I tend to like the comma before better. I think it makes commenting out arbitrary rows easier.

I’ve also seen this sort of construct in SQL Where clauses:

AND field1 = ‘foo’
AND field2 = ‘bar’

Clearly the 1=1 is ridiculous but the point is to make various other lines in the WHERE clause easy to comment out.

Whatever rules for formatting you use and whether you and your team stick to them or not doesn’t really matter that much. The best way to go about it is to use some automatic tool. SQL Server Management Studio has a useful addon called SQL Complete than I use which has a format option.

I’m also a fan of both these websites:

Instant SQL Formatter

This is an online tool with a bunch of options to make it suit your tastes. It’s powered by General SQL Parser which can be incorporated into your application and is available for many programming languages. They also have an addon for SQL Server Management Studio and Visual Studio.

MDX Studio Online

This is a great  tool by Mosha Pasumansky. Mosha is not active in the MDX world anymore having moved on but this website is still going. It kind of crashes if you try and use it a few times but reloading the page works. There is also an application created by Mosha called MDX Studio which will analyse your MDX a bit like StyleCop will analyse your C#. It will even point out potentially slow or unreliable code. It’s especially useful when learning MDX.



Use an automatic tool to parse and format your code.

Leave a Reply

Your email address will not be published. Required fields are marked *