“Above all else, show the data” says Edwarde Tufte. He’s the data visualization expert who promotes a high data-ink ratio in data visualizations. He describes data-ink as “the non-erasable core of a graphic”. In other words, avoid chartjunk.
SQL Junk
I buy that. Less is More. And we can apply that idea to SQL. If SQL is going to be maintained by a human, it’s best to use a style that is easy on the reader. There’s a lot of syntax in SQL that is redundant. By keeping only the non-erasable syntax in SQL statements, the SQL gets easier to understand and maintain.
Square Brackets
I know I’m not the only one who thinks so. I was watching Kendra Little’s presentation SSMS Shortcuts & Secrets. When someone asked whether there was a shortcut for removing brackets. There isn’t. Most people search and replace “[” with “” and then do the same thing with “]”. Kendra wondered if there was a regular expression that allowed a user to do both. There is, but it’s awkward because brackets need to be escaped: [\[\]] (which is only slightly easier to remember than ¯\_(ツ)_/¯).
SQL Junk in SSMS Generated Scripts
SSMS’s scripting engine is great at taking an object like a table and giving you a CREATE statement that will perfectly recreate what you need. But it’s a huge contributor of SQL Junk. I’ve seen so much SSMS-generated SQL get checked in to repositories that the style is assumed to be a best practice.
Here’s something that shows just how much SQL Junk can be removed. I think the resulting SQL is so much more clear and so much easier to maintain.
I think the generated SMO scripts are so nasty. This really should be changed. The brackets are not technically necessary because there is a well-defined list of SQL Server reserved words (and that list can never grow for compat reasons).
Red Gate SQL Prompt has a “remove brackets” feature. Unfortunately, you can’t really use its reformatting feature because the formatting is extremely invasive and looks insane. It’s not incremental but rewrites the entire document removing any customizations that a human might have wanted.
Comment by tobi — September 12, 2017 @ 2:11 pm
They are so nasty. In fact that’s where I got the idea for the gif.
Darkhorse Analytics has a great post called Data Looks Better Naked which demonstrates how to improve a nasty chart by removing chart junk. It’s a good gif because they start with an ugly chart that they came up with. So it’s a bit of a straw man argument. In my case, I didn’t need to set up a nasty example of a CREATE TABLE statement. I let the generated scripts via SSMS do that for me. 🙂
Also, I heard via twitter that CTRL+B+B removes brackets when using SQL Prompt. I’d love to have that in SSMS too.
Michael
Comment by Michael J. Swart — September 12, 2017 @ 2:36 pm
This is more than just a simple “oh shit, this noise makes it hard to read” problem. We old Algol programmers still read square brackets as array subscript declarations.
I was on the ANSI standards committee when we approved double quotes for nonstandard language inserts. We really thought we were clever and thought that other language committees would pick it up. We were about to be the cool kids on the block. But it didn’t work out that way; ISO and Unicode people when with a set of characters consisting of Latin letters, digits, and a subset of possible punctuation marks for everything. This means any ISO standard encoding can be written in any language on earth. This is really handy for portable stuff.
The square brackets are even worse because they are proprietary. They don’t have the same meaning and all the possible ISO and other national standards, or proprietary software as they do in Microsoft land.
We have many many decades of research in typographical readability. Why don’t programming languages bother to read it?
Comment by Joe Celko — September 16, 2017 @ 3:37 pm
You said it. I couldn’t agree more.
Style is not entirely subjective and should be informed by things like the readability studies you mentioned.
Comment by Michael J. Swart — September 16, 2017 @ 3:46 pm
[…] readability. To see a great example of this in action, check out Michael J. Swart’s post Remove SQL Junk (Brackets and Other Clutter). In most cases, you don’t need square brackets in your scripts, but there are instances where […]
Pingback by The Ironic DBA—It's Hip to Be Square - DallasDBAs.com — May 12, 2023 @ 11:14 am