SSMS Results In A Separate Tab

At the end of my SQL Saturday presentation today on Advanced Common Table Expressions, which is about the 12th time I have presented on CTEs, I was asked a question that I have been asked many times after I present.  I am blogging about this because I have been asked about it so many times. This one one of those hidden gems in SSMS that just makes working in SSMS that much easier.

The question was “I have my results and messages showing up below my T-SQL editor in SQL Server Management studio, how do you get it to show up as 3 tabs, one for the editor, one for results and oen for messages.

The default configuration of SSMS your results and messages windows are shown below the query window as shown below:


This configuration is just fine when you are running at a higher resolution, but when you are running at a lower resolution, like most projectors require, it is easier to see what is going on when the Results and Mesages are displayed on separate tabs at the top level as shown in the next two screen shots.



Based on the default configuration, it is not exactly obvious how to do this.

Here is how you do it.  From the Tools menu in SSMS choose Options. The following dialog will be shown.  Expand the Query Results setting, then SQL Server, then click on the Results to Grid (or Results to Text) depending on your preference.  There are two checkboxes that you should check to get the desired behavior.  These are highlighed in the image below.  “Display results in a separate tab”, and “Switch to results tab after the query executes”.  Click both of these then click the OK button.  Depending your your version of SSMS you may need to restart it for the changes to start working.


From this point forward SSMS will default to showing the results in a separate tab.  For SQL Saturday or other presentations this is a great way to share more of the result set with the audience.

3 Steps to Work More Efficiently in SSMS.

Throughout my career I have worked with different programming and database tools.  Thinking back the tools that I enjoyed using were the tools that were easy to get the job done, and the tools that I hated using were the tools that were the most difficult to use. Being good at what I do means mastering the tools so that I can be more productive with my time.

Yesterday at SQL Saturday 198 in Vancouver I learned lots of great things with SQL Server, but there were a couple small things that I picked up on to improve my overall performance when working in SQL Server Management Studio (SSMS).

I thought it might be fun to create a list of the thing that I normally do, along with a few new things that I learned just yesterday at SQL Saturday.

1.  Display results in a separate tab.

This has been my favorite for a couple of years now.  Here is how you set it up.

Open SSMS, and from the top “Tools” menu choose “Options…”.

From the options dialog navigate through the heirarchy to the Query Results, SQL Server, Results to Grid page as shown here.


You will need to check the “Display results in a spererate tab” checkbox, and I suggest that you also check the “Switch to results tab after the query executes”.

You will need to open a new query windows to see this change.  Once it runs you will end up with the results grid taking the entire query window area, and a new tab up top to switch back to your query.  This saves time when viewing results because you need to do less scrolling to see your results.   This is also impacts the execution plan window.  Give it a try it will look something like this.


Once you have this configured, there will be no need to battle with that split screen results again.

2.  Hotkey for SELECT TOP 100 * FROM …

Just learned this one yesterday at SQL Saturday.  Quite often when working in a database, I need to take a look at what some of the data looks like in a table, so I find myself opening a new query window and typing SELECT TOP 100 * FROM Table name. I find myself doing this all the time.  Yesterday I learned a new trick, and that is to set up a keyboard shortcut that allows you to highlighta table name anywhere in a query window, then just hit a shortcut key, in my case CTRL+3, and it runs the SELECT TOP 100 * FROM … query for the table name that is highlighted.

Here is how you do it.   From the options dialog navigate through the heirarchy to the Environment, Keyboard, Query Shortcuts page.


Here, choose the shortcut key you want to map, and enter “SELECT TOP 100 * FROM ” into the Stored Procedure column.  Yes, I know its not a stored procedure, but just give it a try.

Then hit OK on the dialog.  You may need to restart SSMS for this to work.

After restarting, then just highlight a table name in any query editing window and hit the short combination, CTRL + 3 in this example.  This will save me lots of time.

3. sp_help by using Alt+F1

Another big time saver, just highlight the name of a table, then hit Alt+F1, and sp_help will be run against that table name.

Thats my time saving for today.  3 steps to make your SSMS experience more productive.