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.

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.

How To Make Your Queries Perform Like They Used To

Photo by Chad Kirchoff on Unsplash

Prefer watching on YouTube

In the ideal world, you fully test how your SQL Server will handle upgrading to the latest version.  You’re able to catch and mitigate any performance surprises early on.

In the real world, you might not be able to perform a good test before upgrading.  You only learn about poor performance once you upgrade in production.

Does this scenario sound familiar?  It’s what I’ve been thinking about recently as I have to deal with query performance issues after upgrading to SQL Server 2016.

Coming from SQL Server 2012, I know the new cardnality estimator added in 2014 is a major source of the problems I’m experiencing.  While the new cardinality estimator improves performance of some queries, it has also made made some of my queries take hours to run instead of seconds.

Long-term, the solution is to revisit these queries, their stats, and the execution plans being generated for them to see what can be rewritten for better performance.

But ain’t nobody got time for that (at least when facing performance crippling production issues).

Short-term, put a band-aid on to stop the bleeding.

I could change the compatibility mode of the database to revert back to SQL Server 2012 (before the new cardinality estimator was introduced), but then I miss out on being able to use all of the new SQL Server 2016 improvements just because a few queries are misbehaving.

I could enable trace flag 9481 to have my queries use the old cardinality estimator, however as a developer I probably don’t have access to play with trace flags (and for good reason).

Starting with 2016 SP1, what I can do is use the legacy cardinality estimator query hint:

This hint is great because it doesn’t require developers to have any special permissions.  It also allows SQL to use the old cardinality estimator for poor performing queries only – the rest of the server gets to benefit from the improvements brought on by the new cardinality estimator.

With time, I can revisit the queries that are using this hint to try to improve their performance, but in the mean time it’s a great way to fix regressed query performance due to the new cardinality estimator.

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