The Fastest Way To Locate Errors In Your SQL Query

Photo by N. on Unsplash

In about 60 seconds you will never debug error messages in SQL Server Management Studio the same way again.

Coming from a .NET background, I’m used to receiving relatively helpful error messages that point me to the precise location of the error in my code:

Pinpoint error finding

SQL Server Management Studio does a decent job too – except when it doesn’t.  Different versions of SSMS provide error line numbers differently.

Watch this week’s episode on YouTube!

In older versions (<= 2012), SSMS provides error location information relative to the batch starting point of the erroneous query.  This means if you have a window full of T-SQL batch statements, your error message will be relative to the last “GO” in the editor before the syntax error:

In SSMS 2014, batch start locations aren’t considered:

Starting in SSMS 2016, the error messages revert back to counting from the current batch but also indicate the line number where the batch begins:

While all of these error messages point me in the right direction, I’m too lazy to try and remember how each version of SSMS refers to the error location.  This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

You can double click on the error message to be taken directly to the line number referenced!

I know this sounds simple, but it’s one of those small things I never thought of trying until I saw someone else do it.  Little did I know what I was missing out on – especially in those giant hundreds/thousands of line long scripts.  but now that I know about it it’s the only way I locate my query errors.

This functionality also works if you have multiple errors listed in the Messages window.

But what about if your SQL query is all on one giant line (like if it was copied from somewhere or generated dynamically)?  You can use a regular expression to format your query first and then click on the error message to quickly navigate to the correct lines.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

My Favorite SSMS Shortcut (After Copy/Paste)

Photo by Abhishek Desai on Unsplash

If there’s one keyboard shortcut I use more than any other (with the exception of copy and paste) it would be the ALT + highlight multi-line edit/block selection shortcut.

Let’s take a look at three ways the ALT + highlight shortcut lets you work faster in SQL Server Management Studio.

1. Add results of one query into the IN() statement

Sometimes when writing an ad hoc query  you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement…but that’s too much work for a one-time use disposable query.

What you can do instead is:

  1. Copy your values of interest
  2. Paste them into your IN() statement
  3. Hold down the ALT key while dragging the mouse down in front of all of your pasted values
  4. Type a comma (see video above for an easier demonstration).

This allows you to quickly turn pasted values into a list suitable for an IN() statement.

2. Paste values into a template of commands

Sometimes you might have to do something repetitive, like insert a bunch of values into a table.  You could script out multiple value insertion with a UNION ALL and and INSERT INTO statement, but once again that’s a lot of work for a one-time query.

Instead you can copy the values you want to insert by highlighting them while holding down the ALT key then positioning the cursor on the first location where you want to paste the values.  Then once you paste, SSMS will automatically multiline paste the values.

Once again, watch the video above to see a demo – a picture (video!) is worth a thousand words.

3. Quickly modify multiple rows

Finally, you might have ran into a situation where you already have a set of queries written, but they all need to be slightly modified in order to work.  For example, maybe the table name changed and now you need to append a prefix to each table name.

While holding down the ALT key, you can simply drag in front of the text you want to prepend to and then start typing.

Voila! You just edited multiple lines in one go.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

The Quickest Way To Get SQL Command Help

Formula One …. F1 …. Photo by Jp Valery on Unsplash

Every once in a while I discover a SQL Server Management Studio trick that’s apparently been around forever but is completely new to me.

Today I want to point out one of those features that had me thinking “how did I not know about this before”:

The F1 keyboard shortcut.

Prefer video?  Watch this week’s tip on my Youtube channel.

To use it, highlight a command or function that you want to know more information about and then press F1.  Simple as that.

Pressing F1 brings up the Microsoft online documentation for that keyword/function, making it the fastest way of getting to Microsoft’s online documentation.  You’ll solve your own questions faster than a coworker can tell you “to google it.”
Most recently I’ve been using the F1 shortcut in the following scenarios:
  • Can’t remember the date/time style formats when using CONVERT?  Highlight CONVERT and press F1: BOOM! All date and time style codes appear before you.
  • Need to use some option for CREATE INDEX and don’t remember the syntax?  Just highlight CREATE INDEX and press F1!  Everything you need is there.
  • Do you remember if BETWEEN is inclusive or exclusive?  F1 knows.  Just press it.

You get the idea.

Assuming you use the online Microsoft docs 10 times per day, 250 days a year, and each time it takes you 10 seconds to open a browser and search for the doc…

( 10/day * 250/year * 10 sec ) / 60 sec / 60 min = 6.94 hours saved.  Your welcome.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!