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
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.
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.
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.
Barney Lawrence - A Blog 