How to Format Your Queries in SSMS
Microsoft SQL Server Management Studio 22.7 added the ability to format your T-SQL queries by right-clicking and clicking Format SQL, or by hitting control-K, control-Q on your keyboard:
You’re probably not going to like the default formatting out of the box because you’re the kind of person who has strong, passionate feelings and likes to argue about them on the Internet. Good news! The configurations are really flexible. Go into Tools, Options, SQL Formatter:
Keep scrolling:
So much control over what gets put into new lines:
Oh you thought we were done with the new line stuff? No no, homey:
These options are perfect for control freaks like you, my friend. After you’ve got your formatting just the way you like it, click the “Edit user settings as json” at the top of the settings list, and you can copy/your defaults out to save them, pass them around, and trade them with your friends.
To get on the latest SSMS, click Help, Check for Updates. (If your SSMS is so old that it doesn’t have Check for Updates, download it from SQLServerUpdates.com.) After SSMS updates itself and relaunches, you’ll be presented with a slick What’s New screen summarizing the most recent changes, with a table of contents on the left side:
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
This is a great improvement! Almost everyone has their preferences, and what I did not like in particular is that every JOIN is formatted as 3 lines minimum. Would be nice to have some options for that considering all the other options they made. Yes, I’ll add some feedback to MS instead of complaining in your comment section 😉
You’re right, Brent.
We are an ornery bunch of critters, all with our own opinions and standards.
Like I want JOIN as two lines.
And a gutter at column 7
With keywords right-aligned to column 6 and the rest of the clause at column 8.
For me it’s about prevention of mistakes, and ease of checking.
Are the defaults exactly what I like? No.
Are they far, far better than what I am used to seeing from my developers? Yes!
I can’t wait until comma-control arrives. When I first started writing SQL code about twenty years ago I HATED prefixed commas. Now, I can’t stand suffixed commas. We can change!
At another company I had a license to Regdate’s SQL Prompt and really liked its customizability.
Not a fan as it is – yet. In fact I would prefer not to format the code shown in the animated GIF since it would take less time to format the code the way I like it, then to alter the formatted code to my taste.
In Schema Compare (Preview) in SSMS the procedures and functions are shown side by side, not as it is, but ‘formatted’.
You read that you can change the settings to suit you though, right? And then from that point on it’s only two keystrokes to format code.
I did read that, sir. But I do miss quite a few options to bring it close to the desired end result. As others pointed out, the JOINs take multiple lines whereas I – strongly – prefer just one. And I would love to see more indentation options.
OK cool, just making sure. I agree, it’s not perfect, but it’s so much better than hand-formatting stuff, and it’s built into SSMS so I don’t have to ask clients to pay for or install anything. I can at least just say, “Set this up the way you like it before we work together, and then my code will match your standards – or just reformat it when I send you stuff.”
Hi, I’m the person who uses this formatter more than anyone else on the planet. It runs as many as 10,000 times a second on my MacBook Pro (that I bought with my own money) when I run DocsCleaner (a custom .NET command line tool I wrote to automate my workflow) at Microsoft, where I work on the Data Docs team.
This formatter is not perfect. For the longest time it didn’t even support comments. (There’s a reason SQL Prompt is expensive and does more, while SqlScriptDOM is free: turns out Transact-SQL is complicated.)
A few months ago, Drew added comment support. Just like that, it became better. My leet skillz uncovered some comment related bugs and I reported them. Then Drew said “the project takes contributions”, and that’s why one of my favourite bugs I’ve ever written is now also available to all SSMS users!
Fortunately I have since fixed that bug, plus a couple of others I found while brute-testing the formatter. I don’t know when that will be shipped in SSMS but it’s soon.
John Oliver voice THE POINT IS:
SqlScriptDOM is written in such a way that it takes a while to get your head around, but once you do, you can contribute new features that the whole community can use. For free.
As long as your PR includes unit tests and you don’t break the build (and existing unit tests), it’s a matter of weeks before your code is approved, and a matter of months (SSMS ships releases approximately the same time as Visual Studio) to see it in products you use daily.
I have a paid license (yes, own money again) for SQL Toolbelt. There are a few nice features I’d still like to … uh … leverage … from SQL Prompt when I have time. The free and open source library that now does comments, means that if I don’t, someone else can.
Brent, thanks for highlighting one of my favourite new features of SSMS that I had no idea I worked on until it was announced. If any of the people reading this comment want to make the formatter better, the only thing stopping you is inclination and time.
Thanks for putting the work into it! It’s so exciting to see this stuff keep shipping in SSMS, getting better rapidly.