Perfectly Placed Parentheses
14June 27, 2016 by Kenneth Fisher
Like many other programming languages T-SQL uses parentheses () for a number of tasks. To help determine precedence, function calls etc. Now it’s nothing like LISP but with enough in one statement it can still get confusing at times.
Here is a fairly simple T-SQL Example
Intellisense is telling us that something is wrong, but what? If you can tell just by looking more power to you. I personally need help, and this isn’t anything like the worst I’ve ever seen. (Subqueries containing function calls and other subqueries for example.)
You’ll notice that when I go over the parentheses the one I’ve selected and it’s pair turn yellow, unless there isn’t a pair of course. You can also use Ctrl-] to flip between the open and close parenthesis in a pair. This can be particularly useful to make sure that you remembered a close parenthesis at the end of a subquery. In this case that last close parenthesis doesn’t have a match. Now finding out that you are missing an open parenthesis doesn’t mean you know where it’s supposed to go. But you can track the different pairs, making sure that each time you open a parenthesis you close it in the correct place. In this case it belonged right at the beginning.
FYI yellow isn’t the default (it’s a light gray). I find the default hard to see (I’m getting old) so I changed it to yellow in the options under fonts and colors.
Last but not least this particular functionality is called Automatic Matching of Syntax Pairs and will also affect BEGIN/END, BEGIN TRY/END TRY and BEGIN CATCH/END CATCH pairs. At least in the query editor window. There are other pairs in other editors.
Well timed. I was just telling a developer at work this past Friday that his query was not giving the results he thought it was because he had no parameters. His WHERE clause was like “WHERE X = 1 And Y = 2 Or Y = 3”.
He thought it was equivalent to “Where X = 1 And (Y = 2 Or Y = 3)” when in fact it is logically treated as “Where (X = 1 And Y = 2) Or Y = 3”.
Yea, logic can be a real pain. I make a point of using parens any time the logic is more complicated than all ANDs just in case. It also helps me “see” it better in my head.
I always felt parentheses were for clarity, ease of maintenance and (even) documentation. Coming originally from an environment without a debugger and a generated code base, I’m kind of wary about trying to make sense of someone being clever or cute at 3AM. For that matter, trying to explain to someone X amount of time later what was intended with that code.
Well, technically they are to control order of operations but they certainly have the effect of making code more readable etc.
Great feature but has stopped working in out-of-the-box SSMS 2016.
I’ll double check when I get home tomorrow but I’m pretty sure I wrote this using SSMS 2016.
thanks Kenneth for sharing. And in my latest version of SSMS 2016 all is fine.
It wasn’t working in my SSMS 2016 either but just turning it off and back on again seemed to fix it.
Tools > Options > Text Editor > General > Automatic delimiter highlighting
Sorry, it wasn’t the higlighting but the flipping between the open and closing that isn’t working in 2016. Works OK in 2012 and 14 though 😦
Ahh, yea SSMS by default uses the VS 2010 compatible shortcuts. You can switch that back to the regular default by going to options -> keyboard and select default under “Apply the following additional keyboard mapping scheme”
You can look here https://sqlstudies.com/2015/11/16/ssms-setup/ under “Keyboard Layout” to see an image/details.
I think it’s tied to intellisence which is a bit flaky in my opinion. Glad you got it working.
Sorry, it was the flipping between open and closing marks that isn’t working for me, not the highlighting, that’s fine.
Oops, double post, my apologies. Can’t get it working, though have just received an SSMS update…there’s hope yet 🙂 Thanks for looking though.
No problems. Happens to me sometimes too. Worst case roll your own. Try going to the keyboard shortcuts and adding it. Options -> Keyboard -> Edit.GotoBrace and add Ctrl+].