Category: SSMS

SSMS Tips and Tricks: The Amazing Alt Key

Once you know how to use it the Alt key is almost magical, reducing many tedious typing tasks down to a handful of clicks and keystrokes.

I went years without knowing how useful the Alt key was, it’s not well documented but I can guarantee that once you know about it and give it a little practice you won’t be able to live without it.

While I’m filing this under SSMS Tips and Tricks but it works equally in Visual Studio, VS Code, Azure Data Studio and even Notepad ++ (but not plain old vanilla notepad). It’s worth a test in other places too.

What does the Alt Key Do In SSMS?

Holding down the Alt allows you to select an arbitrary block in a text entry area. With that done you can act on the entire selection e.g. by typing, copying, or pasting.

My most common use is adding commas. Have you ever had a list of field names or values that you need to be comma separated? Perhaps you’ve used the Alt + F1 keyboard shortcut to list fields in a table and now you want them in a SELECT statement. Perhaps you have a list of row identifiers of test cases in a large data set that you want to drop into an IN operator so you can see the results. Whatever the need, at some point you’ve probably sat pressing the down arrow and comma key over and over thinking there must be an easier approach (I used to do this kind of stuff in Excel before I knew better).

With the Alt key you can just select every row and type your text into all of them at the same time. A picture is worth a thousand words so here’s an animated gif of the process in action.

Using the Alt key to select multiple rows and type the same values on all of them.

How Does the Alt Key Affect Copying and Pasting?

Alt also allows you to vary how you copy and paste. A normal multi line copy in the clipboard combined with Alt to select multiple rows results in those multiple rows being pasted multiple times. Not often useful unless you want to generate a lot of lines of text quickly.
If you hold down the Alt key to select your copy area then the multiple select is remembered and it fills downward when pasting. Again, this is easier illustrated than described.

Using the Alt key to select multiple rows of text and then paste them to a different location.

Putting It All Together

Once you understand the behaviour you can start to put this together to do something more advanced, this comes in handy where you need to apply the same function to multiple fields and then also need to give those fields unique names. It’s a common and tedious process without the Alt key. With it we can type on multiple lines at the same time for the function and then paste to multiple rows for the field name. The final process looks like this.

Using the Alt key to wrap text in a function and then add the copy and paste the field name for each row.

I couldn’t live without the Alt key now and after a bit of practice doing things like the above are now so embedded into my muscle memory they just happen as I write queries. It’s well worth the effort to get to that point in my opinion, I’m a big fan of anything that reduces a dull task to something quick.

SSMS Tips and Tricks: Custom Keyboard Shortcuts

Why limit yourself to the built in SSMS shortcuts when you can build new ones for yourself?

One of the first things I do when faced with a fresh install of SQL Server Management Studio is to set up a couple of custom keyboard shortcuts.
You’ll find this feature in Options under the Tools menu and then by navigating through Environment > Keyboard > Query Shortcuts

Screenshot of query shortcuts section of SSMS options.

The screenshot above shows my usual additions to the defaults:

Under Ctrl + F1 ‘SELECT TOP 100 * FROM ‘
Under Ctrl + 3 ‘SELECT COUNT(*) FROM ‘

The trailing spaces are important, this won’t work without them.

Feel free to fill in any of the gaps if you’d prefer a different shortcut or one of the above causes issues (I like Ctrl + F1 as it’s easy to hit with one hand but on some machines it ignores the Ctrl and opens up a help page). I’ll assume you’re using the same as me for the rest of this blog.

Once set up, we can open up a new query window and give them a test (they may not be applied to windows already open). How these work is that specified value gets appended to whatever text you have and run as a query. So selecting a table inside a query and hitting our new shortcut gives us either the top 100 rows or a count of rows.

Peeking At Data With Ctrl + F1

Here’s my Ctrl + F1 in action, note how I can use anything that’s valid after a from clause, it can be multiple columns and WHERE clauses too.

Using our new Ctrl + F1 shortcut to peek ad data sets

I use this any time I need to peek at what’s in some table(s) while I’m writing a query to get a feel for the data and to remind me of what’s where.

You don’t have to stick to a TOP 100 in this shortcut but be warned if you get any bigger then things will get slower as there appears to be some sort of performance optimisation applied to values of 100 or below.

Grabbing Metadata With Alt + F1

Recently I’ve become a fan of the built in Alt + F1 option as well, it calls sp_help which amongst other things lists indexes, column names and data types. Don’t have a fancy tool to expand SELECT * into column names? Just use this shortcut and copy and paste from the results.

Counting Rows With Ctrl + 3

The need to count rows comes up a lot for me. Is this a big table I need to be careful with or a smaller table but the real power of this shortcut comes from it’s ability to count across joins.
Have you ever been in a scenario where you have multiple joins and you know one of them is introducing duplicate rows but not which one? Use this to count your starting table and then steadily expand your selection to include more tables until the count changes.
Here’s a simple demo, imagine I wanted customers and their first purchase but it hadn’t dawned on me that a customer might buy more than one item at the same time.

Using our new Ctrl + 3 to get row counts from parts of a query

The above can of course become quite slow if you’re counting a lot of rows but that’s what the cancel query button is for right?

Ctrl + 4 to Ctrl + 0

That leaves a lot of empty spaces that I’m sure can be put to a creative use, I’d love to hear suggestions for other useful shortcuts. The only real limitations are the text needs to fit on a single line and as it must be prefixed on to the selected text and executed we’re looking at simple SQL statements and stored procedures that take a single (or no) parameter.

Feel free to drop suggestions into the comments.